Working with unrelated tables

[Anonymous]

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?

[v-shex-msft · Accepted solution]

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

[Greg_Deckler]

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