Multi level distinct counting

[maashi]

I'm trying to count some customer complaints with multiple filters, and am a bit stumped on how to write this measure. 

Link to sample data:

Freight snapshot.xlsx

I need to count some freight complaints that only have freight related reasons and nothing else.

A unique Sales Order Number can have several lines, and not every line has to have the same Material Return Reason. I want a distinct count of sales orders that contain line(s) of freight, nothing else.  So in the sample data I do not want to count Sales Orders 2017867 or 2009388 because it contains freight and other defect reasons. But I want to count 2016014, 2011927, 2013299, 2009417, 2010615. If there are multiple lines in the same sales order, as long as all the lines are freight, I will count it. (2010615 is a good example of this) 

In other words, if I count the data set, I want the answer to be 5.

Counting all freight lines is easy with a distinct count and a freight filter : 

Freight Count =

CALCULATE(

            DISTINCTCOUNT(

                fact_NCP_Reporting[Sales Order No]),

                FILTER('fact_NCP_Reporting',

                'fact_NCP_Reporting'[NCP Reason Categories.Reason Category]="Freight")

                )

But how do I add a second filter in to look at any other lines the same sales order contains and see if it contains any other return reasons besides freight?

Thanks for your help

[Rakesh1705 · Accepted solution]

1st created the sales order repeatation count

Freight count with respect to sales order no is calculated

Freight count and sales order repeatation number is compared

Net sales order number is calculated

If this solves your problem then accept the same as your solution

[maashi]

This worked perfectly. Thank you very much.

[v-jialongy-msft]

Hi @maashi 

First, you need to create a measure to calculate how many reasons one "sales no" has:

Reason count = 
VAR _no = SELECTEDVALUE(fact_NCP_Reporting[Sales Order No])

RETURN
CALCULATE(DISTINCTCOUNT(fact_NCP_Reporting[NCP Reason Categories.Reason Category]),FILTER(ALL(fact_NCP_Reporting),'fact_NCP_Reporting'[Sales Order No] = _no))

Then modify your original measure:

Freight Count = 
CALCULATE(
            DISTINCTCOUNT(
                fact_NCP_Reporting[Sales Order No]),
                FILTER(ALL('fact_NCP_Reporting'),
                'fact_NCP_Reporting'[NCP Reason Categories.Reason Category]="Freight"&&[Reason count]=1)
                )

Result:

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.