This question seems an easy calculation but my current DAX generates wrong result and need some help.
- Query1 = ‘Cases’, Fields= “Customer Name”, “Case Number”, “CRM Sector”, “Issues” and dates fields;
- Query 2 = ‘Dates’
- Query 3= ‘Mapping_Sector’, Fields = “CRM Sector” and “Industry”
What I would like to accomplish:
- Calculate the count of cases of the specific customer (By Slicer)
Firm (Count) = calculate(DISTINCTCOUNT('Cases'[Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
The result is good - Refer to below screen print Count of Cases by Fiscal Year.
- Calculate the count of cases of the Customer’s Industry - I have problems here
In this example, Customer “Bank of Nova Scotia” (slicer filter value) belongs to “Industry” = “Banking Services”. I need to calculate the count of cases of the Customer’s industry of “Banking Services” but my current DAX calculate the count of all industries as a whole which is not correct.
Sector (count) = Calculate ([Firm (count)], All('Cases'[Customer Name]))
The result is not I expected refer to below screen print: