Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]
)
)
)
)
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |