Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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."
Solved! Go to Solution.
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] )
)
)
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
)
)
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
)
)
Thank you so much for your help
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]
)
)
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?
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] )
)
)
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?
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
)
)
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
)
)
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
"It does not work" means it is giving wrong results or it is still being filtered by the color slicer?
Sorry for the unclear response. it is still being filtered by the color slicer.
@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
)
)
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] )
)
)
@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] )
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |