cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

Issue in Inactive Relationship

Hello,

 

I am facing very wierd issue. I have the following datamodel.

IRDM.png

and I have plot the data from table1 is as follows:IRT.pngwhere Total is a DAX measure.

 

But when I create an inactive relationship between table2 and Dates, the data in the above table changes to:

IRTR.png

I believe that creating inactive relationship between Dates and Table2 shouldn't affect the data from Table1. But it is happening in the report.

 

Please anyone explain me what can be the issue.

 

Here is my attached report :

https://drive.google.com/file/d/1mV8IrY84cbE0VrTTDCdpEUHt-Uv3aAjq/view?usp=sharing

 

Thanks in advance.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Issue in Inactive Relationship

@rajulshah - 

 

A few thoughts:

 

1. A very powerful component of Power BI is auto-filtering based on relationships. In this case, Dates does not filter Table1. Generally you want dimension tables (such as Date) to filter fact tables, such as Table1.

 

2. You will want to "Mark as Date Table" on your Date table.

 

3. In the Total measure, there is a lot of unnecesary logic to figure out the max date. What you are looking for is MAX('Date'[MonthYear]. Also, SELECTEDVALUE only works if a single value is in context. You could re-write with the following measure. Note that multiple rows show up. This is the expected behavior, considering the filtering. If you only want the most recent month, you should filter the date table so that only the most recent month or year is selected. Attributes such as RelativeYear work well for this. 

 

Total = 
VAR EndTransactionMonthYear = max(Dates[MonthYear])
VAR TotalCharges =
    CALCULATE (
        SUM ( Table1[Net Amount] ),
        USERELATIONSHIP(MonthYear[MonthYear],Table1[KeyMonthYear]),
        Dates[MonthYear] <= EndTransactionMonthYear,
//The following has different behavior than the previous line of code - this is quite unexpected! //FILTER( // ALL(Dates), // Dates[MonthYear] <= EndTransactionMonthYear //) ISBLANK(Table1[TDate])=FALSE(), ISBLANK(Table1[SDate])=FALSE(), Table1[IsGreaterThan2013]=1, Table1[Is Aging] = TRUE () ) RETURN //[TotalChargeBefore2014] + TotalCharges

 

3. The Total measure formula is causing the strange behavior of returning a single row in the original. The combination of invoking the inactive relationship and the FILTER on date table causes this. I don't know why this is the case.

 

4. I also don't know know why creating the new relationship causes the behavior to change.

 

Perhaps somebody like @marcorusso or @AlbertoFerrari could explain the strange behavior.

 

Hope this helps,

Nathan

 

View solution in original post

3 REPLIES 3
Highlighted
Super User II
Super User II

Re: Issue in Inactive Relationship

@rajulshah -

That does seem strange. Not sure exactly why that is happening, but one issue that you could resolve:

You can cut out the MonthYear bridge table and create many-to-many relationships directly from the Date table's Month Year column. Make sure to make the filtering single directional.

Hope this helps,

Nathan

Highlighted
Solution Sage
Solution Sage

Re: Issue in Inactive Relationship

Hello @natelpeterson ,

 

Thank you for the solution!

 

The issue has been resolved. But I am concerned about the unexpected behaviour. Is there any concept that I am missing here?

Highlighted
Super User II
Super User II

Re: Issue in Inactive Relationship

@rajulshah - 

 

A few thoughts:

 

1. A very powerful component of Power BI is auto-filtering based on relationships. In this case, Dates does not filter Table1. Generally you want dimension tables (such as Date) to filter fact tables, such as Table1.

 

2. You will want to "Mark as Date Table" on your Date table.

 

3. In the Total measure, there is a lot of unnecesary logic to figure out the max date. What you are looking for is MAX('Date'[MonthYear]. Also, SELECTEDVALUE only works if a single value is in context. You could re-write with the following measure. Note that multiple rows show up. This is the expected behavior, considering the filtering. If you only want the most recent month, you should filter the date table so that only the most recent month or year is selected. Attributes such as RelativeYear work well for this. 

 

Total = 
VAR EndTransactionMonthYear = max(Dates[MonthYear])
VAR TotalCharges =
    CALCULATE (
        SUM ( Table1[Net Amount] ),
        USERELATIONSHIP(MonthYear[MonthYear],Table1[KeyMonthYear]),
        Dates[MonthYear] <= EndTransactionMonthYear,
//The following has different behavior than the previous line of code - this is quite unexpected! //FILTER( // ALL(Dates), // Dates[MonthYear] <= EndTransactionMonthYear //) ISBLANK(Table1[TDate])=FALSE(), ISBLANK(Table1[SDate])=FALSE(), Table1[IsGreaterThan2013]=1, Table1[Is Aging] = TRUE () ) RETURN //[TotalChargeBefore2014] + TotalCharges

 

3. The Total measure formula is causing the strange behavior of returning a single row in the original. The combination of invoking the inactive relationship and the FILTER on date table causes this. I don't know why this is the case.

 

4. I also don't know know why creating the new relationship causes the behavior to change.

 

Perhaps somebody like @marcorusso or @AlbertoFerrari could explain the strange behavior.

 

Hope this helps,

Nathan

 

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors