cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RA Frequent Visitor
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

Accepted Solutions
Highlighted
wildmight2017 Regular Visitor
Regular Visitor

Re: ALLEXCEPT

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 Super Contributor
Super Contributor

Re: ALLEXCEPT

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!

RA Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT

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%
Phil_Seamark Super Contributor
Super Contributor

Re: ALLEXCEPT

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!

RA Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT

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.

Highlighted
wildmight2017 Regular Visitor
Regular Visitor

Re: ALLEXCEPT

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

RA Frequent Visitor
Frequent Visitor

Re: ALLEXCEPT

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 405 members 4,016 guests
Please welcome our newest community members: