Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
malfaizal
Regular Visitor

Need Help to join 2 measure table with different column value

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.

 

malfaizal_0-1663418631353.png

 

The result i get.

malfaizal_1-1663418890274.png

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png

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

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

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

1.png

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.

lazurens2
Frequent Visitor

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 

tamerj1
Super User
Super User

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.

@malfaizal 
Please share the DAX formula for Value1 and Value2

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 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.