Different Measures calculation according to Client/Date

Question

Hi all!!

I'm new to SSAS Tabular Mode and DAX and I was wondering if anyone could help me, please.

I have to create a Measure that uses different formulas accordingly to the Client and Date dimensions.

For instance, I have a Measure named ServiceLevel like below:

ServiceLevel := CALCULATE( DIVIDE ( [SumMeasureA], [SumMeasureB], 0 ) )

But the way this ServiceLevel is calculated differs from client to client.

For instance, Client 1 uses the formula

Client1ServiceLevel := CALCULATE( DIVIDE ( [SumMeasureA], [SumMeasureB], 0 ) )

But Client 2 uses the formula

Client2ServiceLevel := CALCULATE( DIVIDE ( [SumMeasureA], [SumMeasureB] - [SumMeasureC], 0 ) )

And there is more to that equation. If Client 1 decides to change the way they calculate ServiceLevel on 2020-08-01. to something like DIVIDE ( [SumMeasureA], [SumMeasureB] + [SumMeasureC], 0 )

In case this happens, I need to keep history of the ServiceLevel for each client.

So if the service level report is run for dates previous to 2020-08-01, It should use the old formula for Client1, and after 2020-08-01 I should use the new formula.

So It would be something like this

    Client1ServiceLevel :=
    CALCULATE (
        IF (
            MAX ( 'FactTable'[DateId] ) <= 20200801,
            DIVIDE ( [SumMeasureA], [SumMeasureB], 0 ),
            DIVIDE ( [SumMeasureA], [SumMeasureB] + [SumMeasureC], 0 )
        )
    )
    

The question is, how could I build a report like below

Client       Date       Service level    ServiceLevelFormula (wont be in the report, just to explain)
1        2020-07-31     80%                 DIVIDE ( [SumMeasureA], [SumMeasureB], 0 )
1        2020-08-01     87%                 DIVIDE ( [SumMeasureA], [SumMeasureB], 0 )
1        2020-08-02     92%                 DIVIDE ( [SumMeasureA], [SumMeasureB] + [SumMeasureC], 0 )
2        2020-07-31     76%                 DIVIDE ( [SumMeasureA], [SumMeasureB] - [SumMeasureC], 0 )
2        2020-08-01     89%                 DIVIDE ( [SumMeasureA], [SumMeasureB] - [SumMeasureC], 0 )
2        2020-07-31     97%                 DIVIDE ( [SumMeasureA], [SumMeasureB] - [SumMeasureC], 0 )

I would need one single ServiceLevel Measure, right?
So I did it like below:

ServiceLevel :=
CALCULATE (
    SWITCH (
        MAX ( 'FactTable'[Client_ID] ),
        1, IF (
            MAX ( 'FactTable'[DateId] ) <= 20200801,
            DIVIDE ( [SumMeasureA], [SumMeasureB], 0 ),
            DIVIDE ( [SumMeasureA], [SumMeasureB] + [SumMeasureC], 0 )
        ),
        2, DIVIDE ( [SumMeasureA], [SumMeasureB] - [SumMeasureC], 0 ),
        DIVIDE ( [SumMeasureA], [SumMeasureB] , 0 )
    )
)

It worked, but I have the feeling this could become a mess within time as we get more clients and different service levels formulas.

Would you have any suggestions on a better way to implement this?

Answer 1

Hi,
Based on your description, this is the right way to implement your logic in this scene.
To avoid too many messy measures, you could name them with proper tags of your client markers. Create many roles in your tabular model to make sure certain users could only see and use the measure for them. See guide : SSAS Tabular Step-by-step guide on how to hide measures based on role

In case this happens, I need to keep history of the ServiceLevel for each client.

So if the service level report is run for dates previous to 2020-08-01, It should use the old formula for Client1, and after 2020-08-01 I should use the new formula.

I found this request is hard to achieve , so the proper way should be write the data inside the measure statically as you did.

Answer 2

Hi,
Have you resolved this issue ? Did you have any new progress? :)

Answer 3

Hi.

No, but I really considered the point that Alexei has made about aggregation across different formulas.

The date based calculation, we are just keeping the latest formula really. It won't be possible to aggregate data when different formulas are in use (suppose I have a Service Level calculation from January to Jul and another from Aug to Dec - how would I aggregate the whole year Service Level?).

As per Client, I'm doing the validation using the SWITCH. It is unlikely we will need a Service Level evaluation across clients. If we do decided to have an overall Service Level, we would define a specific Service Level formula for this calculation.