SUM based on multiple filters from different tables

[AmudhaKumaran]

Hi Team,

I'm trying to find SUM based on filters from multiple tables that are connected in model.

SUM1 =

    CALCULATE (

        SUM (Table1[Col1] ),

        Table1[Color] IN { "Red", "Yellow" }       

       ,Table2[Type] IN { "Fruit","Vegetable"}

    )

But in this formula, filter from Table2 is not being applied. Please help with the right formula. 

[PC2790 · Accepted solution]

Hello @AmudhaKumaran ,

If  Table 1 and Table 2 have any relationship between the two, you can try using RELATED DAX to apply the filter on Table 2.

Just found a very useful video targetting the same problem:

https://www.youtube.com/watch?v=Wu1mWxR23jU

See if this helps.

[v-cazheng-msft · Accepted solution]

Hi @AmudhaKumaran 

If you want the filters from table2 to filter table1, there must be a relationship path to pass these filters to table1. You can check the relationships in your model to make sure there is a path can pass filters from table2 to table1. Then your dax formula can works well.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PC2790 · Accepted solution]

Hello @AmudhaKumaran ,

If  Table 1 and Table 2 have any relationship between the two, you can try using RELATED DAX to apply the filter on Table 2.

Just found a very useful video targetting the same problem:

https://www.youtube.com/watch?v=Wu1mWxR23jU

See if this helps.

[MarkyMark1]

If using a measure TREATAS is a better option. I think you need a relationship between the tables but it doesn't need to be active.

An example

SalesAmountFiltered =
CALCULATE (
SUM ( Sales[SalesAmount] ),
TREATAS (
VALUES ( Products[Color] ),
Sales[ProductColor]
)
)