topic Re: Need Help to join 2 measure table with different column value in DAX Commands and Tips

Need Help to join 2 measure table with different column value

malfaizal — Sat, 17 Sep 2022 12:48:37 GMT

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.

Re: Need Help to join 2 measure table with different column value

tamerj1 — Sat, 17 Sep 2022 14:46:13 GMT

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

Re: Need Help to join 2 measure table with different column value

malfaizal — Sun, 18 Sep 2022 04:14:16 GMT

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.

Re: Need Help to join 2 measure table with different column value

lazurens2 — Sun, 18 Sep 2022 05:34:18 GMT

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

Re: Need Help to join 2 measure table with different column value

tamerj1 — Sun, 18 Sep 2022 06:27:40 GMT

Hi malfaizal

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

Re: Need Help to join 2 measure table with different column value

malfaizal — Wed, 21 Sep 2022 03:48:01 GMT

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.

Re: Need Help to join 2 measure table with different column value

malfaizal — Wed, 21 Sep 2022 04:27:38 GMT

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

Re: Need Help to join 2 measure table with different column value

malfaizal — Wed, 21 Sep 2022 04:30:02 GMT

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-different-column-value/m-p/2775619/highlight/true#M86642

Re: Need Help to join 2 measure table with different column value

tamerj1 — Wed, 21 Sep 2022 05:29:57 GMT

malfaizal
Please share the DAX formula for Value1 and Value2

Re: Need Help to join 2 measure table with different column value

malfaizal — Wed, 21 Sep 2022 07:58:06 GMT

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

Re: Need Help to join 2 measure table with different column value

tamerj1 — Thu, 22 Sep 2022 04:46:47 GMT

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

Re: Need Help to join 2 measure table with different column value

malfaizal — Thu, 22 Sep 2022 07:43:40 GMT

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