Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Working with unrelated tables

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?

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors