Issue in Inactive Relationship

[rajulshah]

Hello,

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

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

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

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.

[Anonymous]

@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

[rajulshah]

Hello @Anonymous ,

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?

[Anonymous · Accepted solution]

@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