Display Highest Value of Count in a Card

[Anonymous]

I'm trying to display the highest 'id' value in a table with the most appearances (highest number of Count). The attached screenshot shows what I mean. Since the 'customer_id' value of 3220 appears 90 times, I want the card to display 3220 as the value. How do I do this?

 

[v-lili6-msft]

hi @Anonymous 

For your case, just create a measure as below:

Highest Value of Count = 
var _countid=MAXX(ALLSELECTED('Table'[customer_id]),CALCULATE(COUNTA('Table'[customer_id]))) return
CALCULATE(MAX('Table'[customer_id]),FILTER('Table',CALCULATE(COUNTA('Table'[customer_id]))=_countid))

then drag it into a card visual.

here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

[v-lili6-msft]

hi  @Anonymous 

You could copy the formula of measure and just change the table name for yours

Highest Value of Count =
VAR _countid =
    MAXX (
        ALLSELECTED ( 'Table'[customer_id] ),
        CALCULATE ( COUNTA ( 'Table'[customer_id] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[customer_id] ),
        FILTER ( 'Table', CALCULATE ( COUNTA ( 'Table'[customer_id] ) ) = _countid )
    )

Regards,

Lin

[Anonymous]

I did as you instructed but ended up with a (Blank) as a result...

[v-lili6-msft]

hi  @Anonymous 

Could you please share your sample pbix file for us have a test, that will be a great help.

You can upload it to OneDrive for business and post the link here. Do mask sensitive data before uploading.

Regards,

Lin

[Anonymous]

https://techship1-my.sharepoint.com/:u:/g/personal/eric_shao_techship_com/EX3OubkDqtpNrJXzlelYWP4BU1... 

Here is the link. I only kept the table with the two columns needed.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

Ok, for your case, there are some blank [customer_id] in the table, so adjust the formula as below:

Highest Value of Count = 
var _countid=MAXX(FILTER(ALLSELECTED('Sheet1'[customer_id]),[customer_id]<>BLANK()),CALCULATE(COUNTA('Sheet1'[customer_id]))) return
CALCULATE(MAX('Sheet1'[customer_id]),FILTER(VALUES('Sheet1'[customer_id]),CALCULATE(COUNTA('Sheet1'[id]))=_countid))

Result:

Regards,

Lin

[FrankAT]

Hi @Anonymous,

you didn't tell us that your column customer_id contains blank ids. That's messy.

But here is my solution:

Regards FrankAT

[FrankAT]

Hi @Anonymous,

take a look at the following measure:

[Anonymous]

The column [Count of id] does not exist in my data. That column is calculated by doing a Count on the appearances of column [id].

[FrankAT]

Hi @Anonymous,

here is my revised solution:

[1] Only for overview

[2] Card visual with returned value of my sample data

Regards FrankAT

[Anonymous]

Still getting (Blank) as a RETURN value. VAR _HighestCount comes out to be 90 as expected.

[amitchandak]

@Anonymous , Put customer Id on Card

Create a rank measure and put visual level filter Rank =1

Rank =rankx(all(table[customer]),[count of id],,desc,dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

I'm unable to use or interact with any visual level filters that are put on the Card?

https://community.powerbi.com/t5/Desktop/Problem-with-visual-level-filtering-on-card/td-p/31635/page...