Need Help to join 2 measure table with different column value

[malfaizal]

i have do some online research but couldn't get the answer, im trying to join 2 measure table with different value into 1 table, but the result is not what i expected. I would to join table A and table B into the table C. The formula i use is summarizecolumn.

The result i get.

[tamerj1 · Accepted solution]

Hi @malfaizal 
Please refer to attached sample file with the solution

TableC = 
SUMMARIZECOLUMNS (
    'sample_data'[Report_Date],
    'sample_data'[Business],
    'sample_data'[Metric_Code],
    Metric_Value[Level],
    "Metric_Total", CALCULATE ( DISTINCTCOUNT ( 'sample_data'[Ast_netbios] ), ALL ( 'sample_data'[Level] ) ),
    "Metric_Value",
        CALCULATE (
            COUNTROWS ( DISTINCT ( 'sample_data'[netbios] ) ),
            'sample_data'[Level] IN { "P1", "P2", "P3", "P4", "P5" },
            FILTER (
                'sample_data', 
                1 IN { 'sample_data'[P1>5d], 'sample_data'[P2>10d], 'sample_data'[P3>90d], 'sample_data'[P4>180d] }
            )
        ) 
)

[malfaizal]

Superb, this is working for me. Never think of ALL function.

[lazurens2]

Could you please provide a sample pbi document with sample data and measures in place to be able to suggest a solution that works for your case ? 
Thank you 

[malfaizal]

i can only share with you the sample data as shared via link below, as for the measure formula as below:

To get the Total Asset(Value2), i summarize to Report_Date, Business, and Metric_Code:

Metric_Total = 
SUMMARIZECOLUMNS(
'sample_data'[Report_Date],
'sample_data'[Business],
'sample_data'[Metric_Code],
"Metric_Total",
CALCULATE(DISTINCTCOUNT('sample_data'[Ast_netbios])

To get the Total Value(Value1), i summarize to i summarize to Report_Date, Business, Metric_Code and Level with calculate filter:

Metric_Value = 
SUMMARIZECOLUMNS(
'sample_data'[Report_Date],
'sample_data'[Business],
'sample_data'[Metric_Code],
'sample_data'[Level],
"Metric_Value",
CALCULATE(COUNTROWS(DISTINCT('sample_data'[netbios])),
FILTER('sample_data','sample_data'[Level] = "P1" || 'sample_data'[Level] = "P2" || 'sample_data'[Level] = "P3" || 'sample_data'[Level] = "P4" || 'sample_data'[Level] = "P5"),
FILTER('sample_data','sample_data'[P1>5d] = 1 || 'sample_data'[P2>10d] = 1 || 'sample_data'[P3>90d] = 1 || 'sample_data'[P4>180d] = 1 ))+0
)

link to sample data : https://1drv.ms/x/s!An4H6bLOBuqHhKx8gFtwyPBiGXE5QA?e=Re9Fik 

[tamerj1]

Hi @malfaizal 

please try

Table C =
ADDCOLUMNS (
SUMMARIZE ( 'Table', 'Table'[Item1], 'Table'[Item2], 'Table'[Item3] ),
"Value 1", [Value1],
"Value 2", CALCULATE ( [Value2], ALLEXCEPT ( 'Table', 'Table'[Item1], 'Table'[Item2] ) )
)

[malfaizal]

Hi @tamerj1 , 

thank you for your response, but it doesn't work for me, as the table A and table B is a measure table summarize from data table.

Value1 in table A is calculated as distinct count filter to item3 and filter to multiple column based on single value.

While value2 from table B is calculated as distinct count and not following table A data value.

Table A Value1 is total value of the items that having vulnerabilities and Table B Value2 is total item for item1.

I cannot share the raw data as it contains sensitive information, appreciate if there is other solutions that only add the Value2 from table B to as new column to table A based on item1, or any other method that helpful.

[tamerj1]

Hi @malfaizal 

have you tried the solution? Please share the result that you achieve. 

[malfaizal]

Hi @tamerj1 , power bi couldn't find value1 and value2 from table measure a & b.
im working on the sample data, will share it here, appreciate if you could help look into it.

[malfaizal]

Hi @tamerj1 , sample data shared on reply to @lazurens2  

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Need-Help-to-join-2-measure-table-with-differ... 

[tamerj1]

@malfaizal 
Please share the DAX formula for Value1 and Value2

[malfaizal]

Hi @tamerj1 ,

Value2 is a "Metric_Total" as formula below:

Metric_Total = 
SUMMARIZECOLUMNS(
'sample_data'[Report_Date],
'sample_data'[Business],
'sample_data'[Metric_Code],
"Metric_Total",
CALCULATE(DISTINCTCOUNT('sample_data'[Ast_netbios])

Value1 is a "Metric_Value" as formula below:

Metric_Value = 
SUMMARIZECOLUMNS(
'sample_data'[Report_Date],
'sample_data'[Business],
'sample_data'[Metric_Code],
'sample_data'[Level],
"Metric_Value",
CALCULATE(COUNTROWS(DISTINCT('sample_data'[netbios])),
FILTER('sample_data','sample_data'[Level] = "P1" || 'sample_data'[Level] = "P2" || 'sample_data'[Level] = "P3" || 'sample_data'[Level] = "P4" || 'sample_data'[Level] = "P5"),
FILTER('sample_data','sample_data'[P1>5d] = 1 || 'sample_data'[P2>10d] = 1 || 'sample_data'[P3>90d] = 1 || 'sample_data'[P4>180d] = 1 ))+0
)

here also a sample data: https://1drv.ms/x/s!An4H6bLOBuqHhKx8gFtwyPBiGXE5QA?e=Re9Fik