Forum Discussion
Multi level distinct counting
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:
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 =
Thanks for your help
3 Replies
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.
This worked perfectly. Thank you very much.
