Power BI: How to return values from one table based on slicers referencing another table?
I have a Power BI report with two tables in it. Table_1 looks like this:
| ID | Country | Cost | Joining_field |
|---|---|---|---|
| MAR23 | UK | 20 | MAR23_UK |
| MAR23 | ROI | 10 | MAR23_ROI |
| JAN23 | UK | 25 | JAN23_UK |
| JAN23 | ROI | 25 | JAN23_ROI |
Joining_field is a calculated column which is always unique.
Table_2 looks like this:
| Customer | ID | Country | Year | Month | Joining_field |
|---|---|---|---|---|---|
| X | MAR23 | UK | 2023 | MAR | MAR23_UK |
| Y | MAR23 | UK | 2023 | MAR | MAR23_UK |
| Z | MAR23 | ROI | 2023 | MAR | MAR23_ROI |
| X | JAN23 | UK | 2023 | JAN | JAN23_UK |
| Y | JAN23 | ROI | 2023 | JAN | JAN23_ROI |
Joining_field is a calculated column which is not unique.
The two tables have a 1 to many relationship using the joining field in both tables.
On the front page of my report I have three slicers, which filter on the Country, Year, and Month fields from table_2.
I want to create a measure in table_2 which returns the value from the cost column in table_1 based on the values selected in the slicers. So in the example above, if the slicers are set to Country = UK, Year = 2023, and Month = MAR, then the measure should return 20.
I've tried the following DAX without success; it appears to just be summing up the entirety of the Cost column from table_1:
Measure_1 =
VAR V_year = SELECTEDVALUE(table_2[Year])
VAR V_month = SELECTEDVALUE(table_2[Month])
VAR V_country = SELECTEDVALUE(table_2[Country])
RETURN
CALCULATE(SUM(table_1[Cost]),USERELATIONSHIP(table_2[Joining_field],table_1[Joining_field]),
table_2[Year] = V_year,
table_2[Month] = V_month,
table_2[Country] = V_country)
Does anyone know why this is happening, and how I can achieve the required result please?
Use the following measure.
Measure = CALCULATE(SUM(Table_1[Cost]), CROSSFILTER(Table_1[Joining_field], Table_2[Joining_field], Both))
