DAX Measure All Except is not working as expected

[Paramjit]

Date	Category	Specific Filter
01-May-21	A	Great than 40
02-May-21	A	Great than 40
03-May-21	A	Great than 40
04-May-21	A	Great than 40
05-May-21	A	Great than 40
06-May-21	A	Great than 40
07-May-21	A	Great than 40
08-May-21	A	Great than 40
09-May-21	A	Great than 40
10-May-21	A	Great than 40
11-May-21	A	Great than 40
12-May-21	B	Great than 40
13-May-21	B	Great than 40
14-May-21	B	Great than 40
15-May-21	B	Great than 40
16-May-21	B	Great than 40
17-May-21	B	Great than 40
18-May-21	B	Great than 40
19-May-21	B	Great than 40
20-May-21	C	Less than 40
21-May-21	C	Less than 40
22-May-21	C	Less than 40
23-May-21	C	Less than 40
24-May-21	C	Less than 40
25-May-21	C	Less than 40
26-May-21	C	Less than 40
27-May-21	C	Less than 40
28-May-21	C	Less than 40
29-May-21	C	Less than 40
30-May-21	C	Less than 40
31-May-21	C	Less than 40

Hi team,

Need your help for working the below as per requirement.

Requirement: does not apply the 'Specifc Filter' on the 'Total Count' measure but other filter 'Category' should apply. Interection is not the solution as percentage needs to be calcualted from 'Count' and 'Total count'.

Issue: below solution is working untill all the values are selected in the Category filter. 

I have  three measures created as below:

COUNT = COUNT(Sheet1[Date])

TOTAL COUNT = CALCULATE(COUNT(Sheet1[Date]),ALLEXCEPT(Sheet1,Sheet1[Category]))

percentage = DIVIDE([COUNT],[TOTAL COUNT])

there are two slicers on the report

1. Category  ---- A , B, C

2. Specific filter   --- Greated than 40, less than 40

[SivaMani]

@Paramjit, Is it your requirement to calculate Category % or Specific Filter level %?

Correct me if I am wrong,

Based on the sample data, it has 31 records. Great Than 40 has 19 records. Category A has 11. 

Is the expected output 11/31 or 11/19?

[Paramjit]

Thank you Sive for looking into this.

Requiremnt is to calculate the % of specific filter level. 

I have given the four screenshots . 

first screenshot : All the values are selected in both filters. count is 31, total count is 31 and % is 100. Results are correct.

Second screenshot: All the values are selected in Category filter. Only Great than 40 is selected.

Count is  19, Total count is still 31 and % is 61.29%.  Results are correct.

Third screenshot : from Category , B and C are selected. all the values are selected from sepecific filter.

Count is 20 , total count is 20, % is 100. Results are correct.

fourth screenshot: from category, B and C are selected. Less than 40 is selected. 

Count is 12, total count is 12 and % is 100.  Resutls are not expected. count is 12 but total count should be 20 so that % should be 60. 

Thank you,

Paramjit singla

[Paramjit]

last screenshot results are not expecred. total cout should be same as third screenshot, 20.

[SivaMani]

@Paramjit, Really an interesting one!

I think it is working fine.

Let me try to explain, Specific Filter - Less than 40 has 12 records and Category C doesn't have any other Specific Filter.  So when you select Specific Filter as Less than 40, it will filter the records (in this case only Category C) => 12 Records. Since you mention ALL EXCEPT(Table, Table{Category]) in the measure, it will filter Category C.

Hope I am clear.

[Paramjit]

Hi Siva,

Let me try to explain again. 

we just need  (rows count) divied by (total rows count) .  

'rows count' is the count that came after applying all slicers.

'total rows count' is the count that came after applying all slicers except one slicer (in this case 'specifc filter') .  

Please see 3rd and 4th screenshot. ( Total count should be 20 as B and C values are slected in Category slicer. total count should not impected by 'Specific filter' )

Thank you,

Paramjit Singla

[SivaMani · Accepted solution]

@Paramjit,

Try the below steps,

1. Create a table for Specific Filter (It will only have one column with 2 values)
2. Join the new table and existing table using Specific Filter
3. Changes the measure

TOTAL COUNT = CALCULATE([Count],ALL('Specific Filter'[Specific Filter]))

It will work.

[Paramjit]

Thank you @SivaMani  : ) and Congrats, the above solution worked well.

just two questions: 1. should not it work with the way i am doing? All Except should ignore the filter selection but it is not doing. is it a bug ?

2.  can creating a virtual table create performance issue? 

Thank you,

Paramjit

Thank you,

Paramjit singla

[SivaMani]

@Paramjit, I am glad that it worked.

#1, It depends on the cases.

#2,  If you created in Power Query, mostly no harm. It is a best practice to have dimension tables

[Paramjit]

Thank you Siva for all your help...