If multiple relationships are needed to join table A to table B in a calculation?

[owenian5]

I have multiple relationships between table A and table B.

'Year', 'Month', 'Week', and 'Line' are columns linked between these 2 tables.

Table A consist of 'Duration', which I need to sum up.

How can I use "USERELATIONSHIP" to use multiple relationship for my calculation?

I tried the following dax formula, but the visual display an error message:

"Can't display the visual. There are ambiguous paths between table A and table B."

Measure =

CALCULATE(

    SUM( 'TableA'[Duration]),

    FILTER ('TableA', 'TableA'[Category] = "Red"),

    USERELATIONSHIP('TableB'[Year], 'TableA'[Year]),

    USERELATIONSHIP('TableB'[Month], 'TableA'[Month]),

    USERELATIONSHIP('TableB'[Week], 'TableA'[Week]),

    USERELATIONSHIP('TableB'[Line], 'TableA'[Line]),

    )

[tamerj1 · Accepted solution]

@owenian5 

Please use

Measure1 =
CALCULATE (
SUM ( 'TableA'[Duration] ),
FILTER (
'TableA',
'TableA'[Category] = "Red"
&& 'TableA'[Year]
IN VALUES ( 'TableB'[Year] )
&& 'TableA'[Month]
IN VALUES ( 'TableB'[Month] )
&& 'TableA'[Week]
IN VALUES ( 'TableB'[Week] )
&& 'TableA'[Line] IN VALUES ( 'TableB'[Line] )
)
)

[owenian5 · Accepted solution]

Hi tamerj1,

'TableA' does not have a [Color/Category] column.

'TableA' and 'TableB' are linked only by an active relationship between the 'Line' columns.

E..g If red colour is chosen in the slicer filter 'TableB'[Color], using the formula below gives the filtered result SUM('TableA'[Duration]) which is not what I want. I want the unfiltered result, including all colors.  

Measure1 =
VAR B =
    CALCULATETABLE ( 'TableB', ALL ( 'TableB'[Color] ) )
VAR BYears =
    SELECTCOLUMNS ( 'TableB', "@Year", 'TableB'[Year] )
VAR BMonths =
    SELECTCOLUMNS ( 'TableB', "@Month", 'TableB'[Month] )
VAR BWeeks =
    SELECTCOLUMNS ( 'TableB', "@Week", 'TableB'[Week] )

RETURN
    CALCULATE (
        SUM ( 'TableA'[Duration] ),
        FILTER (
            'TableA',
                'TableA'[Year] IN BYears
                && 'TableA'[Month] IN BMonths
                && 'TableA'[Week] IN BWeeks
        )
    )

[owenian5]

Thank you so much for your help

[tamerj1]

Hi @owenian5 

please try

Measure1 =
CALCULATE (
SUM ( 'TableA'[Duration] ),
FILTER ( 'TableA', 'TableA'[Category] = "Red" ),
FILTER (
'TableB',
'TableB'[Year] = 'TableA'[Year]
&& 'TableB'[Month] = 'TableA'[Month]
&& 'TableB'[Week] = 'TableA'[Week]
),
USERELATIONSHIP ( 'TableB'[Line], 'TableA'[Line] )
)

or

Measure1 =
CALCULATE (
SUM ( 'TableA'[Duration] ),
FILTER ( 'TableA', 'TableA'[Category] = "Red" ),
FILTER (
CALCULATETABLE ( 'TableB', USERELATIONSHIP ( 'TableB'[Line], 'TableA'[Line] ) ),
'TableB'[Year] = 'TableA'[Year]
&& 'TableB'[Month] = 'TableA'[Month]
&& 'TableB'[Week] = 'TableA'[Week]
)
)

[owenian5]

Hi @tamerj1 ,

Thanks for your help! 

The year, month and week from Table B are actually slicers on the power BI report. 

So if i use the suggested formulas that you gave, it does not work as the year, month and week are dynamic, based on user's input. (Not pre-defined)

I got this error message when i try your suggested formulas:

A single value for column 'Year' in tableA cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

How can we link TableA[year], [month], [week] to TableB[year],[month], [week] so that the measure can work?

[tamerj1 · Accepted solution]

@owenian5 

Please use

Measure1 =
CALCULATE (
SUM ( 'TableA'[Duration] ),
FILTER (
'TableA',
'TableA'[Category] = "Red"
&& 'TableA'[Year]
IN VALUES ( 'TableB'[Year] )
&& 'TableA'[Month]
IN VALUES ( 'TableB'[Month] )
&& 'TableA'[Week]
IN VALUES ( 'TableB'[Week] )
&& 'TableA'[Line] IN VALUES ( 'TableB'[Line] )
)
)

[owenian5]

Hello, what if there is a filter slicer on the report using 'TableB'[Colour] . Is there a way we can write the DAX measure such that it ignores the colour selected on the slicer?

[tamerj1]

Hi @owenian5 
Please try

Measure1 =
VAR B =
    CALCULATETABLE ( 'TableB', ALL ( 'TableB'[Color] ) )
VAR BYears =
    SELECTCOLUMNS ( 'TableB', "@Year", 'TableB'[Year] )
VAR BMonths =
    SELECTCOLUMNS ( 'TableB', "@Month", 'TableB'[Month] )
VAR BWeeks =
    SELECTCOLUMNS ( 'TableB', "@Week", 'TableB'[Week] )
VAR BLines =
    SELECTCOLUMNS ( 'TableB', "@Line", 'TableB'[Line] )
RETURN
    CALCULATE (
        SUM ( 'TableA'[Duration] ),
        FILTER (
            'TableA',
            'TableA'[Category] = "Red"
                && 'TableA'[Year] IN BYears
                && 'TableA'[Month] IN BMonths
                && 'TableA'[Week] IN BWeeks
                && 'TableA'[Line] IN BLines
        )
    )

[owenian5 · Accepted solution]

Hi tamerj1,

'TableA' does not have a [Color/Category] column.

'TableA' and 'TableB' are linked only by an active relationship between the 'Line' columns.

E..g If red colour is chosen in the slicer filter 'TableB'[Color], using the formula below gives the filtered result SUM('TableA'[Duration]) which is not what I want. I want the unfiltered result, including all colors.  

Measure1 =
VAR B =
    CALCULATETABLE ( 'TableB', ALL ( 'TableB'[Color] ) )
VAR BYears =
    SELECTCOLUMNS ( 'TableB', "@Year", 'TableB'[Year] )
VAR BMonths =
    SELECTCOLUMNS ( 'TableB', "@Month", 'TableB'[Month] )
VAR BWeeks =
    SELECTCOLUMNS ( 'TableB', "@Week", 'TableB'[Week] )

RETURN
    CALCULATE (
        SUM ( 'TableA'[Duration] ),
        FILTER (
            'TableA',
                'TableA'[Year] IN BYears
                && 'TableA'[Month] IN BMonths
                && 'TableA'[Week] IN BWeeks
        )
    )

[tamerj1 · Accepted solution]

Hi @owenian5 
It seems you did not notice my previous reply. I had made a mistake in the above code that was correted in the other reply. Here is the corrected code

Measure1 =
VAR B =
    CALCULATETABLE ( 'TableB', ALL ( 'TableB'[Color] ) )
VAR BYears =
    SELECTCOLUMNS ( B, "@Year", 'TableB'[Year] )
VAR BMonths =
    SELECTCOLUMNS ( B, "@Month", 'TableB'[Month] )
VAR BWeeks =
    SELECTCOLUMNS ( B, "@Week", 'TableB'[Week] )
VAR BLines =
    SELECTCOLUMNS ( B, "@Line", 'TableB'[Line] )
RETURN
    CALCULATE (
        SUM ( 'TableA'[Duration] ),
        FILTER (
            'TableA',
            'TableA'[Category] = "Red"
                && 'TableA'[Year]
                IN BYears
                && 'TableA'[Month]
                IN BMonths
                && 'TableA'[Week]
                IN BWeeks
                && 'TableA'[Line] IN BLines
        )
    )

[owenian5]

Hi tamerj1,

I tried but it does not work.

Background:

Table A does not have a column on color. 

Only Table B has a column on color.

These 2 tables are linked only by an active relationship between the [Line]. No inactive relationship exists.

Problem:

When I select 'red' on the filter  (Table B[Color]), somehow it will filter the Table A[Duration] too.  I use IN VALUES for year, month & week only.

[tamerj1]

@owenian5 
"It does not work" means it is giving wrong results or it is still being filtered by the color slicer?

[owenian5]

Sorry for the unclear response. it is still being filtered by the color slicer.

[tamerj1]

@owenian5 
You are absolutely right. It was a stupid mistake from my side. Please try

Measure1 =
VAR B =
    CALCULATETABLE ( 'TableB', ALL ( 'TableB'[Color] ) )
VAR BYears =
    SELECTCOLUMNS ( B, "@Year", 'TableB'[Year] )
VAR BMonths =
    SELECTCOLUMNS ( B, "@Month", 'TableB'[Month] )
VAR BWeeks =
    SELECTCOLUMNS ( B, "@Week", 'TableB'[Week] )
VAR BLines =
    SELECTCOLUMNS ( B, "@Line", 'TableB'[Line] )
RETURN
    CALCULATE (
        SUM ( 'TableA'[Duration] ),
        FILTER (
            'TableA',
            'TableA'[Category] = "Red"
                && 'TableA'[Year]
                IN BYears
                && 'TableA'[Month]
                IN BMonths
                && 'TableA'[Week]
                IN BWeeks
                && 'TableA'[Line] IN BLines
        )
    )

[owenian5]

Hi tamerj1,

If I want to remove the words in orange (just summing up duration), how do I re-write the DAX formula?

Measure1 =
CALCULATE (
SUM ( 'TableA'[Duration] ),
FILTER (
'TableA',
'TableA'[Category] = "Red"
&& 'TableA'[Year]
IN VALUES ( 'TableB'[Year] )
&& 'TableA'[Month]
IN VALUES ( 'TableB'[Month] )
&& 'TableA'[Week]
IN VALUES ( 'TableB'[Week] )
&& 'TableA'[Line] IN VALUES ( 'TableB'[Line] )
)
)

[tamerj1]

@owenian5 
Not sure if I correctly understand. Do you mean like:

Measure1 =
CALCULATE (
    SUM ( 'TableA'[Duration] ),
    FILTER (
        'TableA',
        'TableA'[Category] <> "Orange"
            && 'TableA'[Year]
                IN VALUES ( 'TableB'[Year] )
                    && 'TableA'[Month]
                        IN VALUES ( 'TableB'[Month] )
                            && 'TableA'[Week]
                                IN VALUES ( 'TableB'[Week] )
                                    && 'TableA'[Line] IN VALUES ( 'TableB'[Line] )
    )
)