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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
InsHunter
Helper II
Helper II

Correct measure to use to mapping in Bridge table

Hi,

Being a new bie to PBI Need help to arrive at correct measure to take in to account the bridging table .
Recreated my actual case with simple sample data.
Two fact tables,Three dim tables and a Bridge table.
Bridge table has n to 1 relationship as well as 1 to n relation ship between ID1 and ID2

InsHunter_0-1693023027639.png

 


My current measure does not take in to account the relation ship while calculation as it still calculates the values for independent IDs  and not taking the ID  mapping to account. Expected correct values given in red after aggregation.

InsHunter_1-1693023574871.png

https://drive.google.com/file/d/1NwijYKBDzng82mm6jXpthB8SkWAG1OJo/view?usp=drive_link 

Enclosing the PBIX sample file.
Deeply appreciate your support.

Thanks.

1 ACCEPTED SOLUTION

Yeah, sort of.  The good thing about TREATAS is that it works (better) without joins.

View solution in original post

5 REPLIES 5
InsHunter
Helper II
Helper II

@lbendlin -Thanks for your suggestion

InsHunter
Helper II
Helper II

@lbendlin 
Request to confirm whether the below approach is right in current context

Energy_per_cum(Rev1) = 
CALCULATE(

VAR Units =
CALCULATE(
    [Energy (Units)],
    TREATAS(
        SUMMARIZE(
            Bridge_Table,Bridge_Table[ID1]),
            Fact_Table1[ID1])
)

VAR Flow_cum  = 
CALCULATE(
    [Flow (Ltrs)],
    TREATAS(
        SUMMARIZE(
            Bridge_Table,Bridge_Table[ID2]),
            Fact_Table2[ID2]
    )
)

VAR Units_per_Cum = DIVIDE(Units,Flow_cum)

RETURN Units_per_Cum
)

Yeah, sort of.  The good thing about TREATAS is that it works (better) without joins.

lbendlin
Super User
Super User

Are you familiar with the TREATAS function?

@lbendlin -Sorry i need to explore. If you can suggest that measure,will be of great help. Thank u for your response

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors