DAX code to count orders with crossed categories

jormaga

New Member
Joined
May 25, 2016
Messages
2
I've got a list of units which are being sold by a shop. Each line describes date, order number, number of units and category (A and B in this example).
How can I, using just DAX measures in a pivot table, know how many orders are both in categories A and B per day? I've tried using CALCULATE in several ways, but I can't find the correct way to do it...

Here below there is a sample table

mIGmz.jpg


Thanks a lot!

Jorge
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
If it wasn't clear through the post, let me say that the pivot table should show:

25/05/2016 3 (orders 100, 104 and 105 have units from categories A and B)
26/05/2016 1 (order 200 has units from categories A and B)


Thanks
 
Upvote 0
Hi
Try with
Code:
CALCULATE(DISTINCTCOUNT('Table1'[OrderNo]),
    FILTER(
        SUMMARIZE(FILTER('Table1', 'Table1'[Category]="A" || 'Table1'[Category] = "B"),
            'Table1'[OrderNo],"categories",DISTINCTCOUNT('Table1'[Category])
        
        ),
        [categories]=2
    )
)
Regards,
 
Last edited:
Upvote 0
Or simpler
Code:
COUNTROWS(
    FILTER(
        SUMMARIZE(FILTER('Table1', 'Table1'[Category]="A" || 'Table1'[Category] = "B"),
            'Table1'[OrderNo],"categories",DISTINCTCOUNT('Table1'[Category])
        
        ),
        [categories] = 2
    )
)
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top