DAX how to Calculate Count of cases by customer's industry When using Customer Name in the Slicer

Hello Fellow Power BI Users -

This question seems an easy calculation but my current DAX generates wrong result and need some help.

Basic Information:

What I would like to accomplish:

  1. 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.

  1. 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:

Thank you.

Update, I have added “Industry” field to Cases query by merging two tables and created below calculation to count the cases of Customer’s Industry.

Rev_Sector(Count) = Calculate(COUNTROWS('cases'), all('cases'), values('Cases'[Sector (merge)]))

Ok great, yes that that’s the formula I would have advised using.


Unfortunately, the calculation in my second post apparently is Grand Total of all Fiscal Years and is not correct. The correct result should be varied based on different fiscal year. I am wondering how to achieve this?

Try using ALLSELECTED(‘cases’), to see if that keeps the filter applied by having Fiscal Year on the column headings.


Thank you, Unfortunately Allsected still cannot get the expected result. Instead I i am working on Variable to see if it is going to work.

See below updates

  • Use this version to calculate ‘Industry’ aggregate based on Slicer selection

    Firm (Inquiries) = calculate( DISTINCTCOUNT('Cases'[Case Number]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
      Sector (Inquiries)_Rev 3 = VAR SECTOR=CALCULATETABLE(VALUES('Cases'[Sector Name (merge)]), USERELATIONSHIP('Cases'[Date Search Inquiry], Dates[Date]))
          Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR) 
  • Result is good when Firm is not blank:

  • Result is not good because then ‘Firm’ is blank then the Industry is blank, this is not correct

  • Any ideas?

Is it possible to see an example file here? Finding this a bit too difficult to work out without seeing everything in play and testing a few options.