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
mebarr
Employee
Employee

DAX: Calculating with related tables and filters

I'm having issues trying to calculate a total in one table that is related to several tables and I am trying to use some filters and ignore others in my total.

 

I have a page level filter based on certain products (table A). Secondly, I have a slicer by geography (table B). My data for total sold is in table C. Table C is related to Table A and B.
I'm trying to calculate total sales from Table C, whereby I'm ignoring the product filter in Table A to include all products, but I want to keep the geography filter in Table B

My equation is MYTOTAL = CALCULATE(sum('Table C'[Total]),ALLEXCEPT('Table C', 'Table C'[Geography Tie Code]))

The column Geography Tie Code connects to table B's column that filters product by page view.
The equation works for including all products; however, when I include filters in the geography slicers, I'm not able to get the total for just the selected geography - it's including all geographies.

Any thoughts to adjust the equation? Thanks!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Can you clarify this a bit and maybe post some relevant sample data? You have a page filter for products Table A but then you want to ignore it? Can you just get rid of it or is it useful for other visualizations on the page.

 

I believe the issue with your formula is that your ALLEXCEPT needs to refer to Table B, what your slicer is based off of. I *think* that is why it is not working.

 

If you look at the example for ALLEXCEPT:

https://support.office.com/en-US/article/ALLEXCEPT-Function-DAX-f31a3742-bafe-4af3-9c24-f08439eca85d

 

 

The ALLEXCEPT clause refers to the DateTime table, not the summing table,


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

Can you clarify this a bit and maybe post some relevant sample data? You have a page filter for products Table A but then you want to ignore it? Can you just get rid of it or is it useful for other visualizations on the page.

 

I believe the issue with your formula is that your ALLEXCEPT needs to refer to Table B, what your slicer is based off of. I *think* that is why it is not working.

 

If you look at the example for ALLEXCEPT:

https://support.office.com/en-US/article/ALLEXCEPT-Function-DAX-f31a3742-bafe-4af3-9c24-f08439eca85d

 

 

The ALLEXCEPT clause refers to the DateTime table, not the summing table,


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.