topic DAX Measure for previous values based on dimension in DAX Commands and Tips

DAX Measure for previous values based on dimension

TOK — Tue, 29 Mar 2022 10:54:47 GMT

I am struggeling with creating a measure with DAX.
I have an existing measure per two dimensions (ChainStoreID, ClusterID)

Now I want my measure to follow two simple rules.

1. If there is a blank value, get the next filled value (ordered by ClusterId) for this chainstore.

eg. there are three blank values (116, 1 ; 117, 7 ; 117, 8). As 116, 1 has no lower ClusterId, it should remain empty.
ClusterId 7 and 8 for ChainStoreId 117 should be filled with 87.

2. The current value should be the same or higher than the value of the previous clusterId.

eg. ClusterID 6 (88) is lower than ClusterId 5 (93), so it should be 93.

Current situation:

Expectation:

ChainStoreId	1	2	3	4	5	6	7	8
116		72	74	87	93	93	93	93
117	68	77	79	87	88	88	88	88

Re: DAX Measure for previous values based on dimension

Jihwan_Kim — Tue, 29 Mar 2022 11:44:31 GMT

Hi,

Please check the below picture and the attached pbix file.

Value measure: = SUM( Data[Value] )

Desired outcome measure: = VAR currentclusterid = MAX ( 'Cluster'[ClusterID] ) VAR currentchainstoreid = MAX ( ChainStore[ChainStoreID] ) VAR currentvalue = [Value measure:] VAR maxvalueperchainstore = MAXX ( FILTER ( ALL ( Data ), Data[ChainStoreID] = currentchainstoreid && Data[ClusterID] <= currentclusterid ), Data[Value] ) RETURN IF ( currentvalue <= maxvalueperchainstore, maxvalueperchainstore, currentvalue )

Re: DAX Measure for previous values based on dimension

tamerj1 — Tue, 29 Mar 2022 12:21:24 GMT

Hi TOK
Here is a sample file with the solutionhttps://www.dropbox.com/t/n3qHIrFTHyVIexTR

You can create a new calculated column

New Value = VAR CurrentValue = Data[Value] VAR CurrentClusterId = Data[ClusterId] VAR CurrentStoreTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ChainStoreId] ) ) VAR MaxValuePerStore = MAXX ( CurrentStoreTable, Data[Value] ) VAR ClusterIdOfMaxValue = CALCULATE ( MAX ( Data[ClusterId] ), CurrentStoreTable, Data[Value] = MaxValuePerStore ) VAR Result = IF ( CurrentValue < MaxValuePerStore && CurrentClusterId > ClusterIdOfMaxValue, MaxValuePerStore, CurrentValue ) RETURN Result

Re: DAX Measure for previous values based on dimension

TOK — Tue, 29 Mar 2022 13:04:29 GMT

I tried both of your solutions and a calculated column worked best for me.

But I guess my example wasn't specific enough. It works for Max(Value) having Max(ClusterID), but thats not always the case. ChainStoreID = 113 should return 78 for ClusterID 4 and 5, but our if clause won't return the expected as 78 is not max(Value) nor max(ClusterId).

Thanks for your help!

Re: DAX Measure for previous values based on dimension

tamerj1 — Tue, 29 Mar 2022 13:15:04 GMT

TOK
Ok you mean the maximum previous value right? This can be fixed

Re: DAX Measure for previous values based on dimension

tamerj1 — Tue, 29 Mar 2022 13:30:31 GMT

TOK
Try this https://www.dropbox.com/t/fEVKUullXZ43Unud

New Value = VAR CurrentValue = Data[Value] VAR CurrentClusterId = Data[ClusterId] VAR CurrentStoreTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[ChainStoreId] ) ) VAR PreviousClustersTable = FILTER ( CurrentStoreTable, Data[ClusterId] < CurrentClusterId ) VAR MaxValuePerStore = MAXX ( PreviousClustersTable, Data[Value] ) VAR ClusterIdOfMaxValue = CALCULATE ( MAX ( Data[ClusterId] ), PreviousClustersTable, Data[Value] = MaxValuePerStore ) VAR Result = IF ( CurrentValue < MaxValuePerStore && CurrentClusterId > ClusterIdOfMaxValue, MaxValuePerStore, CurrentValue ) RETURN Result