topic If multiple relationships are needed to join table A to table B in a calculation? in DAX Commands and Tips

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

Anonymous — Wed, 22 Feb 2023 00:49:00 GMT

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

)

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

tamerj1 — Tue, 21 Feb 2023 14:49:21 GMT

Hi Anonymous

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

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

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

Anonymous — Tue, 21 Feb 2023 15:32:24 GMT

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?

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

tamerj1 — Tue, 21 Feb 2023 16:09:36 GMT

Anonymous

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

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

Anonymous — Wed, 22 Feb 2023 01:09:44 GMT

Thank you so much for your help

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

Anonymous — Wed, 08 Mar 2023 00:07:18 GMT

Hi tamerj1,

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

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

tamerj1 — Wed, 08 Mar 2023 06:10:12 GMT

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

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

Anonymous — Wed, 29 Mar 2023 09:52:26 GMT

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?

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

tamerj1 — Wed, 29 Mar 2023 10:41:53 GMT

Hi Anonymous
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 ) )

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

Anonymous — Thu, 30 Mar 2023 08:12:18 GMT

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.

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

tamerj1 — Thu, 30 Mar 2023 08:12:01 GMT

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

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

Anonymous — Thu, 30 Mar 2023 08:13:32 GMT

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

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

tamerj1 — Thu, 30 Mar 2023 08:20:31 GMT

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

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

Anonymous — Fri, 31 Mar 2023 06:52:54 GMT

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

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

tamerj1 — Fri, 31 Mar 2023 07:12:18 GMT

Hi Anonymous
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