Forum Discussion
TOK
Helper II
Helper IIDAX 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...
TOK
Try this https://www.dropbox.com/t/fEVKUullXZ43UnudNew 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
tamerj1
Community Champion
Community ChampionTOK
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