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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
yodha
Helper IV
Helper IV

How to write a Measure using two tables?

Hi, we have two tables in pbix, "Table1" and "Table2".

can anyone help me writing a "Measure" for this requirement? that can replace table 1 values with table 2 (there is no relatonship between tables).

Thanks in advance.

 

 

2 ACCEPTED SOLUTIONS
Icey
Community Support
Community Support

Hi @yodha ,

 

Please check:

 

1. Try to replace value in Power Query editor.

= Table.ReplaceValue(#"Changed Type",each [Value], each if [Group]=false and [Type] = false and [#"Sub-type"]=false then (let Name_  = [Name] in Table.SelectRows(#"Table 2", each [Name] = Name_ and [Type] = "new")){0}[Value] else [Value],Replacer.ReplaceValue,{"Value"})

replace.PNG

 

 

2. Or you can create a calculated column or a measure in Power BI Desktop.

Column = 
IF (
    'Table 1'[Group] && 'Table 1'[Type]
        && 'Table 1'[Sub-type],
    'Table 1'[Value],
    CALCULATE (
        SUM ( 'Table 2'[Value] ),
        FILTER (
            'Table 2',
            'Table 2'[Name] = 'Table 1'[Name]
                && 'Table 2'[Type] = "new"
        )
    )
)

column.PNG

Measure = 
IF (
    SELECTEDVALUE ( 'Table 1'[Group] ) && SELECTEDVALUE ( 'Table 1'[Type] )
        && SELECTEDVALUE ( 'Table 1'[Sub-type] ),
    SUM ( 'Table 1'[Value] ),
    CALCULATE (
        SUM ( 'Table 2'[Value] ),
        FILTER (
            'Table 2',
            'Table 2'[Name] = SELECTEDVALUE ( 'Table 1'[Name] )
                && 'Table 2'[Type] = "new"
        )
    )
)

measure.PNG

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

mwegener
Most Valuable Professional
Most Valuable Professional

Hi @yodha ,

 

I would go step by step.

1. Generate columns in table 2 that you need to link to table 1. (Conditional Columns)

2. Merge the tables over these columns together.

3. Find the desired value using a conditional column.

 

Take a look at the attached PBIX.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @yodha ,

 

Please check:

 

1. Try to replace value in Power Query editor.

= Table.ReplaceValue(#"Changed Type",each [Value], each if [Group]=false and [Type] = false and [#"Sub-type"]=false then (let Name_  = [Name] in Table.SelectRows(#"Table 2", each [Name] = Name_ and [Type] = "new")){0}[Value] else [Value],Replacer.ReplaceValue,{"Value"})

replace.PNG

 

 

2. Or you can create a calculated column or a measure in Power BI Desktop.

Column = 
IF (
    'Table 1'[Group] && 'Table 1'[Type]
        && 'Table 1'[Sub-type],
    'Table 1'[Value],
    CALCULATE (
        SUM ( 'Table 2'[Value] ),
        FILTER (
            'Table 2',
            'Table 2'[Name] = 'Table 1'[Name]
                && 'Table 2'[Type] = "new"
        )
    )
)

column.PNG

Measure = 
IF (
    SELECTEDVALUE ( 'Table 1'[Group] ) && SELECTEDVALUE ( 'Table 1'[Type] )
        && SELECTEDVALUE ( 'Table 1'[Sub-type] ),
    SUM ( 'Table 1'[Value] ),
    CALCULATE (
        SUM ( 'Table 2'[Value] ),
        FILTER (
            'Table 2',
            'Table 2'[Name] = SELECTEDVALUE ( 'Table 1'[Name] )
                && 'Table 2'[Type] = "new"
        )
    )
)

measure.PNG

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

mwegener
Most Valuable Professional
Most Valuable Professional

Hi @yodha ,

 

Why don't you use Power Query?

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


@mwegener Yeah, Powerquery also works, but am new to power BI and dont know how to get expected result using power query.Thank you!

mwegener
Most Valuable Professional
Most Valuable Professional

Hi @yodha ,

 

I would go step by step.

1. Generate columns in table 2 that you need to link to table 1. (Conditional Columns)

2. Merge the tables over these columns together.

3. Find the desired value using a conditional column.

 

Take a look at the attached PBIX.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast - Power BI Tutorials


Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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