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

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

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
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.