Using both primary (active) and secondary (inactive) relationship in a measure?

[Clara]

Hi all!

Here's my problem: I have two tables, and each of them have a Date and a Unit column. I've created a date table and successfully set up a relationship between the two tables through their "Date" column, but not through their "Unit" column (multi-multi, both have the same distinct values).

Right now I need to create a report filtering (with slicers) by both Date (which works) and Unit (which doesn't) and show the results of measures which use both columns as parameters. For example, here's one of them (calculates last year sales based on current date slicer -- when selecting feb 2019, it should show total sales of feb 2018):

SalesTotal_LY = CALCULATE(SUM(Table1[MonthlySales]),SAMEPERIODLASTYEAR(Calendar[MonthYear]),ALLEXCEPT(Table1,Table1[Unit],Table1[MonthYear]),Table1[ProductID]="001")

This measure results in blank (as do the others). What am I doing wrong?

For reference, my Units slicer comes from Table2 (measures referencing only Table2[Unit] and Calendar[MonthYear] seem to work just fine) and my Date slicer comes from Calendar[MonthYear].

[Anonymous]

hi @Clara 

Have you tried Userelationship?

USERELATIONSHIP(Table1[column]; table2[column])

[Clara · Accepted solution]

I have! I might have figured it out.

I decided not to use SAMEPERIODLASTYEAR (though I'm not sure it has any part in making it work) and instead ended up with this measure:

SalesTotal_LY =
VAR LastYr = DATEADD(Calendar[MonthYear],-1,YEAR)
RETURN CALCULATE(
       SUM(Table1[Sales]),
       USERELATIONSHIP(Table1[Units],Table2[Units]),
       Table1[MonthYear]=LastYr,
       FILTER(ALLEXCEPT(Table1,Table1[MonthYear]),
       Table1[Units]=[RelUnit]),
       Table1[ItemId]="001")

[RelUnit] looks like this (I'm not even sure I need it, but haven't tried taking it out yet:

RelUnit = CALCULATE(MIN(Table1[Units]),USERELATIONSHIP(Table1[Units],Table2[Units]))

[Anonymous]

Okay, good. I think it should do the same thing but its often easier to use dateadd when you're unsure. Are you able to verify the result?