Forum Discussion
ALLEXCEPT
I am trying to get the average rate that a Diagnosis is selected across Connected Device and Product Type. I will have slicers that will filter the table based on different product dimensions ie Product Launch Year, Series, Size etc. I do not want the Product Dimension slicers to impact the average rate. The only column that should impact the rate is the date filter.
Volume of Diagnosis Code per Connected Device / Product Type
= CALCULATE(COUNTROWS('Cases'),
ALLEXCEPT('Cases','Cases'[Connected Device],'Cases'[DateTimeOpened],'Cases'[Product Type],'Cases'[Diagnosis]))
Total Volume of Records per Connected Device / Product Type
CALCULATE(COUNTROWS('Cases'),
ALLEXCEPT('Cases','Cases'[Connected Device],'Cases'[DateTimeOpened],'Cases'[Product Type]))
I used ALLEXCEPT function under the impression that the slicers for the product dimensions would not affect the values, but it does not seem to be working. Any ideas as to how I can work around this?
Try to read up on bridge tables. Such as this - https://www.sqlbi.com/daxpuzzle/multiple-many-to-many-relationships/ - this should help you to get started
6 Replies
- Phil_Seamark
Microsoft Employee
HI RA
In theory, what you have described should be working perfectly, so there might be something subtly different with your report. Can you share a cut down (obfuscated) version?
I have many measures and slicers configured the way you describe and they work perfectly.
Hi Phil_Seamark,
I am using 2 data sources.
Sample of first data source:
CaseNumber DateTimeOpened Model Diagnosis 123 1/1/2018 Model A Issue 1 456 1/2/2018 Model B Issue 2 789 1/3/2018 Model C Issue 3 234 1/4/2018 Model D Issue 3 567 1/5/2018 Model A Issue 2 890 1/6/2018 Model B Issue 3 345 1/7/2018 Model C Issue 3 678 1/8/2018 Model D Issue 2 901 1/9/2018 Model A Issue 3 321 1/10/2018 Model B Issue 1 654 1/11/2018 Model C Issue 2 987 1/12/2018 Model D Issue 3 The second data source contains a lookup table to retrive the product dimensions:
Model Launch Year Series Connected Device Product Type Size Model A 2017 Series 1 Yes Type 1 10 Model B 2017 Series 2 No Type 2 20 Model C 2018 Series 1 Yes Type 1 30 Model D 2018 Series 2 No Type 2 40 I am using the RELATED function to add the product dimensions into the first data source.
Assuming '2017' is selected in the Product Year slicer, I would expect to see Model C and Model D removed from the table (but not from the measure results)
Model A with Issue 1
Diagnosis Volume: I would expect to see a count of all Connected Device 'Yes' and Product Type 'Type 1' with Diagnosis 'Issue 1'
Total Volume: I would expect to see a count of all Connected Device 'Yes' and Product Type 'Type 1' (All Issue types)
Model Diagnosis Diagnosis Volume Total Volume Rate Model A Issue 1 1 6 17% Model B Issue 2 2 6 33%
But what I actually see:
The measure value results do not include Model C and D.
Model Diagnosis Diagnosis Volume Total Volume Rate Model A Issue 1 1 3 33% Model B Issue 2 1 3 33% - Phil_Seamark
HI RA
If I understand your requrements properly then this PBIX file must be close
https://1drv.ms/u/s!AtDlC2rep7a-ox0ymg0j-_RLHoms
I don't use ALLEXCEPT or RELATED, so take a look and let me know what you think.
