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.
Hi
Im new to Power BI and have been stuck on this problem for a few days.
I can do what im after in Excel just not Power Bi.
I have attached sample data to show where im trying to get to rather than having to explain.
Basically i have 1 table and from the fact table i want to know the best way to go about counting ID's if it = "X" and count the "ID's" if count of of X is >1. and display this value in a card visual and be able to filter by months on the dashboard.
https://drive.google.com/open?id=1mrLGAp015rGkkUZwxiOFhAc2gYsH9TrB
Any help or advice welcome!
Solved! Go to Solution.
Hi @vieyepee,
I've downloaded your data and had a go. This is one way to solve it - create the following measure:
More than 1 Consultation =
VAR Shortlist =
FILTER(
SUMMARIZE(
'Table',
'Table'[ ID ],
"Total", COUNT( 'Table'[ ID ] )
),
[Total] > 1
)
RETURN
COUNTROWS(Shortlist)
Here's my table by ID with the measure, and a card:
This will be filter aware on the date, so if I change the slider, I get a revised count, e.g.:
If you were to use a date table to group by month, this will aggregate as required.
Hopefully this should be all you need. Good luck!
Daniel
If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
Hi i was wondering if you could help me with a another measure.
Based on this measure, instead of counting the ID, can we sum up the Total for any total >1 ?
More than 1 Consultation =
VAR Shortlist =
FILTER(
SUMMARIZE(
'Table',
'Table'[ ID ],
"Total", COUNT( 'Table'[ ID ] )
),
[Total] > 1
)
RETURN
COUNTROWS(Shortlist)
Hi @vieyepee,
I've downloaded your data and had a go. This is one way to solve it - create the following measure:
More than 1 Consultation =
VAR Shortlist =
FILTER(
SUMMARIZE(
'Table',
'Table'[ ID ],
"Total", COUNT( 'Table'[ ID ] )
),
[Total] > 1
)
RETURN
COUNTROWS(Shortlist)
Here's my table by ID with the measure, and a card:
This will be filter aware on the date, so if I change the slider, I get a revised count, e.g.:
If you were to use a date table to group by month, this will aggregate as required.
Hopefully this should be all you need. Good luck!
Daniel
If my post solves your challenge, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂
Proud to be a Super User!
My course: Introduction to Developing Power BI Visuals
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
AWESOME WORK Daniel! Thanks for your help, Exactly what i was after!
Thanks so much!
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |