Forum Discussion
Helper ISUM based on multiple filters from different tables
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.
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.
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.
3 Replies
v-cazheng-msftCommunity Support
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.
PC2790Community Champion
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.
MarkyMark1Advocate I
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]
)
)