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.
Hello,
I've tried so many variations I've found on this forum and stackoverflow. None appear to work for me (and I'm lacking DAX experience to manipulate them to work how I need, sorry for that)
I have a table like the example below, and I am trying to create a card visual to show the distinct count of reference if the number of rows for that reference is over X
For example
User selects between dates 2019-05-01 and 2019-05-31
Reference count >= 2 <-- this is not user selectable, so can be hardcoded into formula.
Reference | Date |
-----------------------------
ABCDE | 20190501 |
ABCCC | 20180202 |
ABDDD | 20190502 |
ABCCC | 20190503 |
ABCCC | 20190504 |
ABDDD | 20180101 |
AB123 | 20190520 |
AB123 | 20190521 |
AB123 | 20190522 |
Results to count
------------------
ABCCC <-- This has 2 rows and so gets through the filtering
AB123 <-- this has 3 rows and so gets through the filtering
The card will now show the number 2 because I am looking to count the number of distinct reference values. This will change each time the user changes the date range.
Hope my explaination makes sense.
Thanks for any support.
ps. I have posted a similar message before, ( https://community.powerbi.com/t5/DAX-Commands-and-Tips/Measure-Counting-Observations-by-Reference-ch... ) however this solution works when I want to show the results in a table because the table can show another column which is full of one's (1) and it shows the total at the bottom. It doesn't work when I want to use a card visual.
Solved! Go to Solution.
Hi @RobThrive
Try this measure
Measure = VAR RefCount_ = 2 // Modify as necessary RETURN SUMX ( DISTINCT ( Table1[Reference] ), INT ( CALCULATE ( COUNT ( Table1[Date] ) ) >= RefCount_ ) )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Hi @RobThrive
Try this measure
Measure = VAR RefCount_ = 2 // Modify as necessary RETURN SUMX ( DISTINCT ( Table1[Reference] ), INT ( CALCULATE ( COUNT ( Table1[Date] ) ) >= RefCount_ ) )
Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.
Cheers
Thank you so much @AlB . I've spent all weekend trying to learn and understand how to achieve what I needed. This is the perfect solution and something for me to learn to understand!
Have a good week!
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |