How to write a Measure using two tables?

[yodha]

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.

[Icey · Accepted solution]

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"})

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"
        )
    )
)

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"
        )
    )
)

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.

[Icey · Accepted solution]

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"})

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"
        )
    )
)

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"
        )
    )
)

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]

Hi @yodha ,

Why don't you use Power Query?

[yodha]

@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 · Accepted solution]

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.