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


#1

Hello Sam,
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.


#2

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)]))


#3

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

Thanks


#4

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?


#5

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

Cheers
Phil


#6

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.


#7

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]))
          RETURN
          Calculate ([Firm (Inquiries)], All('Cases'[Firm Name]), SECTOR) 
    
  • Result is good when Firm is not blank:
    2018-11-28_15-54-50%20DNA

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

  • Any ideas?


#8

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.

Thanks