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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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