Forum Discussion

TOK's avatar
TOK
Icon for Helper II rankHelper 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

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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
    )