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.
Hello, I am stuck trying to solve the following:
I have one fact table, and I need to build a measure (not a calculated column) which counts all rows with the following criterias:
1) Distinct 'TurID'
2) MAX of 'SekvensNo' (within every 'TurID')
3) IF 'GruppeNo' is 3 or higher number (on the row with the MAX 'SekvensNo' for every 'TurID')
That is, I should have a count that equals the rowcount if the Highest 'SekvensNo' for a given 'TurID' contains a value of 3 or higher in the 'GruppeNo'. If that value is 2 or lower, the 'TurID' should not be counted.
Example below should return 3.
'TurID' 103 and 105 will not be counted, as they both have the value 2 in 'GruppeNo' for the Max 'SekvensNo' (2 and 3 respectively).
Any suggestions will be greatly appreciated.
Best Regards
Solved! Go to Solution.
@Anonymous , Try a measure like
calculate(sum(Table[SekvensNo]), filter(Table,Table[SekvensNo] >2 && Table[SekvensNo] = calculate(max(Table[SekvensNo]), allexpcept(Table,table[TurlID]))))
@Anonymous , Try a measure like
calculate(sum(Table[SekvensNo]), filter(Table,Table[SekvensNo] >2 && Table[SekvensNo] = calculate(max(Table[SekvensNo]), allexpcept(Table,table[TurlID]))))
Thank you so much for your quick response, @amitchandak !
I took your suggestion, modified it a little bit, and now it seems to be working perfectly (need a little more validation and testing by me). Would you please read through my modified formula and see if it looks correct to you?
Again, thank you! 🙂
Modified Measure =
CALCULATE (
DISTINCTCOUNT ( Table[TurID] ),
FILTER (
Table,
Table[GruppeNo] > 2
&& Table[SekvensNo]
= CALCULATE (
MAX ( Table[SekvensNo] ),
ALLEXCEPT (
Table,
Table[TurID]
)
)
)
)
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |