Best way to calculate measure line by line ?

[Cocow]

Hi everyone !

I'm in a complex DAX time-optimization operation today, and I need your help ! (sorry for the syntax errors, I'm not english native-speaker)

For context : We are using measure to aggregate gross revenue and visits in the different stores (it's basically sums)

The stores are then grouped by if they are physical and selling only one brand, physical selling several brand or online stores

With these measures, we then calculate the progress for each (this year's sum versus last year's sum)

And finally, with the progresses we calculate a delta between gross revenue progress (GMP) and visits progress (VP) (It's basically GMP - VP)

The main problem is that on the delta, I have to exclude the online stores (because visits doesn't make sense in an online store)

And because of this exclusion, the measure takes a very long time (1m20) to calculate and display in a table in my report

The DAX formula used today to achieve the point is :

SWITCH(SELECTEDVALUE(Store[Format]),
"physicalOneBrand",

CALCULATE(([GRP]-[VP]) * 100, FILTER(Store, Store[Format] = "physicalOneBrand")),
"physicalSeveralBrands",

CALCULATE(([GRP]-[VP]) * 100, FILTER(Store, Store[Format] = "physicalSeveralBrands")),
CALCULATE(([GRP]-[VP]) * 100, FILTER(Store, Store[Format] = "physicalOneBrand" || Store[Format] = "physicalSeveralBrands" )))

Is there a better way to calculate this delta without taking 1min+ to display the result ?

Thanks in advance for your help, I really have no solution

If the explanation isn't clear, tell me and I'll try to give you more mocks to help the understanding !

[MAwwad]

-- Define a variable for the store format
VAR StoreFormat = SELECTEDVALUE(Store[Format])

-- Define a variable for the physical store formats
VAR PhysicalStoreFormats = { "physicalOneBrand", "physicalSeveralBrands" }

-- Define a variable for the GMP-VP delta
VAR GMP_VP_Delta = ([GRP]-[VP]) * 100

-- Return the GMP-VP delta for physical store formats, otherwise BLANK
RETURN
IF(
CONTAINS(PhysicalStoreFormats, StoreFormat),
CALCULATE(GMP_VP_Delta, ALL(Store)),
BLANK()
)

[Cocow]

Hello, thanks for your answer I didn't think of using the variables ! It takes only 20s now to load !

However, this case doesn't take in account the "resumed" value, because for one brand, I would like to show the global value for my delta like that :

Brand 1	Blank now when I want a result for the concerned stores
Brand 1 - PhyscalOneBrand	GMP_VP_Delta
Brand 1 - PhysicalSeveralBrands	GMP_VP_Delta
Brand 1 - OnlineStores	Blank
Brand 2	Blank now when I want a result for the concerned stores
Brand 2 - PhyscalOneBrand	GMP_VP_Delta
Brand 2 - PhysicalSeveralBrands	GMP_VP_Delta
Brand 2 - OnlineStores	Blank

Can I just add an option on the PhysicalStoreFormats to take in account the fact that several values can be selected ? Or will that make a delta taking the online stores in account for the resumed value ?

Thanks in advance and thanks for your help already !

[v-rzhou-msft]

Hi @Cocow ,

Due to I don't know your data model, here I will give you some suggestion. According to your sample, I think it should be a matrix with hierarchy level and "Brand1"/"Brand2" should be in the first level. Currently, I think your requirement is to show items in level1. I think you can try ISINSCOPE().

Measure =
IF (
    ISINSCOPE ( Store[Level2] ),
    [Your Measure before],
    IF ( ISINSCOPE ( Store[Level1] ), [New measure you need] )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.