Measure that count rows for every disctinct value of one column, max of another column etc

[Anonymous]

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

[amitchandak · Accepted 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]

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]
)
)
)
)