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,
Hopefully someone can help me with the following. I have a column with ID's that can have multiple values for another column. I'd like to count/filter all ID's that have a specific value for that other column. Small example:
Column with ID | Classification |
A | X |
B | Y |
C | <other> |
A | X |
B | <other> |
C | Y |
I would like to get all ID's that have value X AND Y in column "Classification". How can I do that?
Looking forward to your suggestions!
KR,
Joost
Solved! Go to Solution.
Couldn't quite make the above solutions work. It has something to do with the fact that ID's reoccur a lot. I had to pivot the table and then count for X and Y if they were > 0 for the ID's in the table. If I created a X AND Y > 0 I could get the cross section I need. Thanks all for the support!
Couldn't quite make the above solutions work. It has something to do with the fact that ID's reoccur a lot. I had to pivot the table and then count for X and Y if they were > 0 for the ID's in the table. If I created a X AND Y > 0 I could get the cross section I need. Thanks all for the support!
Hi @joostvanham ,
Create a measure as below and add it to visual filter to filter measure = 1 and you will get the IDs that have value X AND Y.
measure =
var count_x = CALCULATE(DISTINCTCOUNT('Table'[Column with ID]),FILTER(ALLEXCEPT('Table','Table'[Column with ID]),'Table'[Classification]="X"))
var count_y = CALCULATE(DISTINCTCOUNT('Table'[Column with ID]),FILTER(ALLEXCEPT('Table','Table'[Column with ID]),'Table'[Classification]="Y"))
return
IF(count_x>0 && count_y>0, 1, 0)
If you want a count of these IDs, you could create a sumx() measure.
Measure 2 = SUMX('Table',[measure])
Best Regards,
Jay
I had a similar issue and this has resolved it. Thank you so much
Hi @joostvanham
You can try creating the following measure:
Hi Preeti,
Thank you! Your solution gives me OR X OR Y rather than the ID's that have an occurance for both X AND Y. Any thoughts there?
Kindest regards,
Joost
Hi @joostvanham
As per your requirement you might try this :
@joostvanham
Create this measure:
Count =
CALCULATE (
COUNT( TABLE[ID]),
TABLE[Classification] IN {"X","Y"}
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
Thank you! Your solution gives me OR X OR Y rather than the ID's that have an occurance for both X AND Y. Any thoughts there?
Kindest regards,
Joost
Based on your sample data, my calculation will give you a result of 4 as it counts x and y.
If you need the distinct count then you can change the COUNT to DISTINCTCOUNT
can you share the expected result and how it should be calculated otherwise ?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |