cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
InsHunter
Advocate I
Advocate I

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
Advocate I
Advocate I

@lbendlin -Thanks for your suggestion

InsHunter
Advocate I
Advocate I

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors