Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have a problem which should be simple, but I cannot get the correct result.
The source table is
Order ID | Product | Product category |
1111 | Millers | Beer |
1111 | VAT69 | Liquor |
2222 | Bud | Beer |
3333 | Jack Daniels | Liquor |
4444 | Carlsberg | Beer |
4444 | Heineken | Beer |
4444 | Johhnie Walker | Liquor |
5555 | Ballentines | Liquor |
and as a result, I need to get the count of Order numbers having one Product category only:
Product category | Order count |
Beer | 1 |
Liquor | 2 |
(Orders 1111 and 4444 should not be counted since they include both categories).
The real tables have more than two product categories.
Any ideas on how I can accomplish this?
Thank you!
Solved! Go to Solution.
@Laurix ,
I would do this in two steps:
First create a new Calculated Column
NumberofCategories = CALCULATE( DISTINCTCOUNT( [Product category] ),
ALLEXCEPT( 'Beer&Liquor','Beer&Liquor'[Order ID] ))
Order IDProductProduct categoryNumberofCategories
1111 | Millers | Beer | 2 |
1111 | VAT69 | Liquor | 2 |
2222 | Bud | Beer | 1 |
3333 | Jack Daniels | Liquor | 1 |
4444 | Carlsberg | Beer | 2 |
4444 | Heineken | Beer | 2 |
4444 | Johhnie Walker | Liquor | 2 |
5555 | Ballentines | Liquor | 1 |
Second step is to create a new Measure:
OrderCount = CALCULATE( COUNT( 'Beer&Liquor'[NumberofCategories] ),
FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))
There is probably a way to do this all in one step, but at least this will hopefully get you started.
Regards,
@Laurix ,
Please try this as your Measure:
OrderCount = CALCULATE( DISTINCTCOUNT( 'Beer&Liquor'[Order ID] ),
FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))
Regards,
Hello @Laurix , I do have similar solution provided by @rsbin .
Please look at it for your information:
Create two measure to get count and filter:
Measure one DAX looks like this:
Counting = CALCULATE(COUNT('Counting Data'[Order ID]),ALLEXCEPT('Counting Data','Counting Data'[Order ID]))
Measure two DAX looks like this:
Final Count =
CALCULATE('Counting Data'[Counting],ALLEXCEPT('Counting Data','Counting Data'[Product category]),FILTER('Counting Data','Counting Data'[Counting]=1))
Output looks like this:
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
Thanks for providing this solution.
Same as for the other solution, it does not work well when there are only products of same category on an order.
For this case, the solution proposed indicates for order 6666 2 products of same category, thus, ingoring them in the "Final count" measure.
The answer should be
Beer 2
Liquor 2
How can this be adjusted to work for this case as well?
Many thanks!
@Laurix ,
I would do this in two steps:
First create a new Calculated Column
NumberofCategories = CALCULATE( DISTINCTCOUNT( [Product category] ),
ALLEXCEPT( 'Beer&Liquor','Beer&Liquor'[Order ID] ))
Order IDProductProduct categoryNumberofCategories
1111 | Millers | Beer | 2 |
1111 | VAT69 | Liquor | 2 |
2222 | Bud | Beer | 1 |
3333 | Jack Daniels | Liquor | 1 |
4444 | Carlsberg | Beer | 2 |
4444 | Heineken | Beer | 2 |
4444 | Johhnie Walker | Liquor | 2 |
5555 | Ballentines | Liquor | 1 |
Second step is to create a new Measure:
OrderCount = CALCULATE( COUNT( 'Beer&Liquor'[NumberofCategories] ),
FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))
There is probably a way to do this all in one step, but at least this will hopefully get you started.
Regards,
Interesting solution. Thanks for sharing it!
However, for a particular case it does not work well. Please see below:
Order 6666 contains only Beer products and should be counted as 1, instead of 2.
How can this be corrected?
Thank you very much!
@Laurix ,
Please try this as your Measure:
OrderCount = CALCULATE( DISTINCTCOUNT( 'Beer&Liquor'[Order ID] ),
FILTER( 'Beer&Liquor', 'Beer&Liquor'[NumberofCategories] = 1 ))
Regards,
Nice, works well now. Many thanks!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |