Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |