Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RA
Frequent Visitor

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?

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
Phil_Seamark
Employee
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.

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

I am using 2 data sources.

 

Sample of first data source:

CaseNumberDateTimeOpenedModelDiagnosis
1231/1/2018Model AIssue 1
4561/2/2018Model BIssue 2
7891/3/2018Model CIssue 3
2341/4/2018Model DIssue 3
5671/5/2018Model AIssue 2
8901/6/2018Model BIssue 3
3451/7/2018Model CIssue 3
6781/8/2018Model DIssue 2
9011/9/2018Model AIssue 3
3211/10/2018Model BIssue 1
6541/11/2018Model CIssue 2
9871/12/2018Model DIssue 3

 

 

The second data source contains a lookup table to retrive the product dimensions:

ModelLaunch YearSeriesConnected DeviceProduct TypeSize
Model A2017Series 1YesType 110
Model B2017Series 2NoType 220
Model C2018Series 1YesType 130
Model D2018Series 2NoType 240

 

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)

ModelDiagnosisDiagnosis VolumeTotal VolumeRate
Model AIssue 11617%
Model BIssue 226

33%

 

But what I actually see:

The measure value results do not include Model C and D.

ModelDiagnosisDiagnosis VolumeTotal VolumeRate
Model AIssue 11333%
Model BIssue 21333%

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.  

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark,

 

Thank you so much for the time and effort you have put into this. Unfortunately, this solution will not work for my needs. There are hundreds of Connected device/ Product Type/ Diagnosis combinations and I would need to see the rate across all of them.

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

Hi @wildmight2017,

 

Briefly glancing at it, it looks like this may be the problems I am running into. It is all starting to make some sense now. I will look into it in more depth.

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.