Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all, sorry my subject is confusing but I'm trying to only flag or sum a value if it is a Max Stop Number grouped by a Location and a Date. I want to return a value of 50 for each time this matches so I can sum up those 50's dynamically.
Data Looks like this
Stop Number | Date | Location | Measure |
1 | 1/1/2020 | A | |
2 | 1/1/2020 | A | 50 |
1 | 1/10/2020 | A | |
2 | 1/10/2020 | A | |
3 | 1/10/2020 | A | |
4 | 1/10/2020 | A | 50 |
1 | 1/1/2020 | B | |
2 | 1/1/2020 | B | 50 |
So if someone summed this up above by Jan 1st, then Location A and B would have values of 50. If they summed by Jan 1st through the 10th then Location A would have 100 while Locaiton B would have 50
I have tried the below but for whatever reason I can't sum the 1's that are returned or multiply it by anything to get what i want.
Value(IF(
CALCULATE(MAX(Stops[StopNumber]),ALLEXCEPT(Stops[date],Stops[Location]))
=MAX(
Stops[StopNumber]) ,1 ,0))
Solved! Go to Solution.
Hi @Anonymous ,
Would you please create a measure to return values you want(50) for max stop number based on date and location:
Measure =
VAR m =
CALCULATE (
MAX ( 'Table'[StopNumber] ),
ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Location] )
)
VAR b = 50
RETURN
IF ( m IN DISTINCT ( 'Table'[StopNumber] ), b, 0 )
SUM it based on location by the measure below:
SUMSTOPNUM = SUMX(ADDCOLUMNS('Table',"maxstopnum",[Measure]),[maxstopnum])
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdK2LkA643VNhBGJI1uMawEBETvuLMMhyH8XD188oH76ng?e=ERsica
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |