topic Working with unrelated tables in DAX Commands and Tips

Working with unrelated tables

Anonymous — Wed, 13 Jul 2022 11:51:52 GMT

Hi,

Context- I have 10 tables; out of which 4 tables are related to cost and there exist many to many relationship among them. There is 1 table about project name and ID. The ID column is also in all the tables but not for all rows.

Summary- A messed up data model that works for a few reports that I don't want to change

Question- The user selects the project ID and I have to fetch all the cost related data if the project ID exist in the table. Is there any way to handle via DAX?

Re: Working with unrelated tables

Greg_Deckler — Wed, 13 Jul 2022 13:10:05 GMT

Anonymous Yes, you can use TREATAS or CALCULATE with a FILTER clause that identifies the project code, etc. Many ways to do this.

Re: Working with unrelated tables

Anonymous — Tue, 19 Jul 2022 02:18:12 GMT

HI Anonymous,

You can add a variable to store the selection from the slicer and use the 'IN' operator with this variable as a condition in a filter to calculate with the target table records.

formula = VAR selected = VALUES ( Table[Column] ) RETURN CALCULATE ( SUM ( Table2[Sales] ), FILTER ( ALLSELECTED ( Table2), [Column] IN selected ) )

Regards,

Xiaoxin Sheng