Forum Discussion

TOK's avatar
TOK
Helper II
4 years ago
Solved

DAX Measure for previous values based on dimension

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 bl...
  • tamerj1's avatar
    tamerj1
    4 years ago

    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

     

     

TOK's avatar
TOK
Helper II
4 years ago

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!

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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