Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@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
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!
@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
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
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
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |