Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Solved! Go to 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
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
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
I did as you instructed but ended up with a (Blank) as a result...
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
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.
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
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
Hi @Anonymous,
take a look at the following measure:
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].
Hi @Anonymous,
here is my revised solution:
[1] Only for overview
[2] Card visual with returned value of my sample data
Regards FrankAT
Still getting (Blank) as a RETURN value. VAR _HighestCount comes out to be 90 as expected.
@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
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...
User | Count |
---|---|
85 | |
75 | |
71 | |
69 | |
55 |
User | Count |
---|---|
98 | |
96 | |
92 | |
78 | |
70 |