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,
I'm working on a analysis and my dataset consist of to ID columns: [Product_ID] & [Category_ID]
I want to find out what products have records of more than one category.
Example with the desired output [Check multiple categories]:
Product_ID | Category_ID | Check multiple categories |
001 | 001 | False |
002 | 001 | True |
002 | 002 | True |
003 | 001 | False |
003 | 001 | False |
In this example we have 2 Categories, 3 products. Product 2 is in the dataset with both categories. How can I identify the records that have more than one category?
I've been trying this solution and my own variation's of it. It doesn't seem to work. The dataset consists of 44K rows.
Can you please help me figure out a way to do this?
Thanks, Isa
Solved! Go to Solution.
@Isa-TheHague try this DAX expression
Column =
VAR __c = CALCULATE ( DISTINCTCOUNT ( dup[Category_ID] ), ALLEXCEPT ( dup, dup[Product_ID] ) )
RETURN
__c > 1
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Isa-TheHague , create a new column like
new column= calculate( distinctcount(Table[Category_ID ]) ,filter(Table, [Product_ID] = earlier([Product_ID]) ) )
I am also working on something similar, I am able to create a flag using provided solutions. But I want it as a slicer( if I try to use the calculate measure as a slicer it's not working to filter out same product present in differnt category) . How to use slicer for the same problem ?can anyone help me with the solution
@Isa-TheHague , create a new column like
new column= calculate( distinctcount(Table[Category_ID ]) ,filter(Table, [Product_ID] = earlier([Product_ID]) ) )
Thanks! This also works and gives back the number categories product is in. Actually both your and @parry2k solution's combined give me great insights in my data.
@Isa-TheHague try this DAX expression
Column =
VAR __c = CALCULATE ( DISTINCTCOUNT ( dup[Category_ID] ), ALLEXCEPT ( dup, dup[Product_ID] ) )
RETURN
__c > 1
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
It worked, Hero!!
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 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |