Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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] }
)
)
)
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] }
)
)
)
Superb, this is working for me. Never think of ALL function.
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
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
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] ) )
)
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.
Hi @malfaizal
have you tried the solution? Please share the result that you achieve.
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.
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...
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |