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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.