cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Clara Member
Member

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Clara Member
Member

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

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

 

View solution in original post

3 REPLIES 3
Roha83 Member
Member

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

hi @Clara 

Have you tried Userelationship?

 

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

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

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

 

View solution in original post

Roha83 Member
Member

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

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?

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 60 members 935 guests
Please welcome our newest community members: