How to write a Measure using two tables?

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.

 

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.