Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I have dataset with orders. Some orders have products from only one category, other orders have products from multiple categories. I'd like to show how many orders have products from multiple categories and how many orders have products from only one category. Additionaly I'd like to have possibility to see orders that contains selected category.
I tried with DISTINCTCOUNT and it showed orders with multiple categories but I couldn't filter by selected category. Is there any solution?
Example data table is below:
order | product | division |
b1 | 12 | a |
b1 | 21 | b |
b1 | 22 | b |
b2 | 12 | a |
b2 | 21 | b |
b2 | 22 | b |
b2 | 32 | c |
b3 | 32 | c |
b3 | 32 | c |
b4 | 12 | a |
b4 | 32 | c |
b5 | 32 | c |
b6 | 21 | b |
b6 | 22 | b |
Thanks for any hints!
Solved! Go to Solution.
Hi @JarekkWr ,
Try the following measure:
Measure = CALCULATE(DISTINCTCOUNT('Table'[order]),FILTER(ALLSELECTED('Table'),'Table'[product]=MAX('Table'[product])))
It will return what you want!
Wish it is helpful for you!
Best Regards
Lucien
Hi @JarekkWr ,
Try the following measure:
Measure = CALCULATE(DISTINCTCOUNT('Table'[order]),FILTER(ALLSELECTED('Table'),'Table'[product]=MAX('Table'[product])))
It will return what you want!
Wish it is helpful for you!
Best Regards
Lucien
@JarekkWr , Plot this measure with product
countx(filter(summarize(Table, Table[product], "_1", distinctCOUNT(Table[division])), [_1]>1), [product])
Change the column in measure as per need
Thanks @amitchandak , unfortunately this measure gives no result, even on my sample data 😞
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |