cancel
Showing results for
Did you mean:
Highlighted
Microsoft

## 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

Accepted Solutions
Super User IV

## Re: DAX: Calculating with related tables and filters

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,

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Super User IV

## Re: DAX: Calculating with related tables and filters

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,

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!