Return MAX of a Distinct Count per Category

[TotallyWillem]

This is a (simplified) example of my data (the data I use has over 100.000 entries):

 

Here is what I want to do:

I need to make a count of all the ID's where is the highest attempt '310' and it needs to work with the slicer on 'week'.
I tried: 

CALCULATE(DISTINCTCOUNT(Blad1[ID]); FILTER(Blad1;MAX(Blad1[Attempt])); Blad1[Status] = 310)

But this returns a count of all the 310 (but it should be 1, because only ID=3 has 310 at the highest attempt).

Does anybody knows how to code this?

[TotallyWillem]

@Zubair_Muhammad & @AlB

I'm sorry but both aren't the solution, but this is my fault since the example data had one flaw.

In my real data, there are ID's who are handled in the same week. I've updated the data in the post with an extra ID (6) which gives a better example.

[jdbuchanan71]

@TotallyWillem

Can you give a sample of your actual data, pasted as a table not a picture, and the number you are expecting to see in a week with multiple 310's as well as other types?

[TotallyWillem]

@jdbuchanan71Well my actual data has 35 columns and 301.433 rows. FYI this is a database of phonecalls we do as a business (I work at a contactcenter) and every row is an attempt. We create 1250 rows per hour of data.

@AlB

"On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run some tests and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here." I will from now on 😉 thanks for your help

@Zubair_Muhammad

This works great! Thanks. I've never used TOPN, so i just learned a whole new function 🙂

[Zubair_Muhammad · Accepted solution]

@TotallyWillem

In the revised case, this modification hopefully will work

Measure =
VAR LastweekTable =
    GENERATE (
        SELECTCOLUMNS ( VALUES ( Blad1[ID] ), "ID_", [ID] ),
        CALCULATETABLE ( TOPN ( 1, Blad1, [Week], DESC, [Attempt], DESC ) )
    )
RETURN
    COUNTROWS ( FILTER ( LastweekTable, [Status] = 310 ) )

[AlB]

@TotallyWillem

I had misread your requirements and was just looking for IDs whose max value in 'Status' was 310.  So you're correct that it only worked by chance given the particularities of your sample data. We have to complicate it a bit then: 

Measure4 = 
SUMX (
    DISTINCT ( Blad1[ID] );
    VAR _MaxAttempt =
        CALCULATE ( MAX ( Blad1[Attempt] ) )
    RETURN
        INT (
            CALCULATE ( DISTINCT ( Blad1[Status] ); Blad1[Attempt] = _MaxAttempt ) = 310
        )
)

It looks like @Zubair_Muhammad's solution is looking for the Status in the latest week. Should work with a minor modification.

On a different note, please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run some tests and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.

Cheers

[Zubair_Muhammad]

@TotallyWillem

Try this one

Measure =
CALCULATE (
    DISTINCTCOUNT ( Blad1[ID] ),
    TOPN ( 1, BLAD1, [Week], DESC ),
    Blad1[Status] = 310
)

[TotallyWillem]

@Zubair_Muhammad
This returns as 'empty' (not sure what it states in English, I use the dutch version of PBI)

[Zubair_Muhammad]

@TotallyWillem

Sorry. Try this one

Measure =
VAR LastweekTable =
    GENERATE (
        SELECTCOLUMNS ( VALUES ( Blad1[ID] ), "ID_", [ID] ),
        CALCULATETABLE ( TOPN ( 1, Blad1, [Week], DESC ) )
    )
RETURN
    COUNTROWS ( FILTER ( LastweekTable, [Status] = 310 ) )

[TotallyWillem]

Great this does the trick!

[AlB]

Hi @TotallyWillem

An alternative to the elegant solution suggested by @Zubair_Muhammad:

Measure2 =
SUMX (
    DISTINCT ( Blad1[ID] );
    INT ( CALCULATE ( MAX ( Blad1[Status] ) ) = 310 )
)

[TotallyWillem]

@AlBThis is a good alternative and works almost the same. I like how this one returns '0' when there is nothing instead of 'empty', but I'm trying to figure out how this works, since it doesn't use the 'attempt' column?