CODEWITHSUNDEEP
powerbidaxdata-modelingpowerbi-desktop
Pratik Bhavsar
Pratik Bhavsar

Reputation: 848

Inactive relationships affecting measures

I have the following tables & relationships in our pbix report: enter image description here

For some obvious reasons, I need to have a relationship (non-active) between Dates[date] and Table2[T2Date]. However, doing so causes data fluctuation to measure 'Total Amount' in Table1.

Here are some screenshots: enter image description here

Before Relationship (Dates[date] - Table2[T2Date]):

enter image description here

After Relationship (Dates[date] - Table2[T2Date]):

enter image description here

I need to understand why this difference is coming up and how the relationship is causing it since the measure uses a different relationship.

For reference, I am attaching the pbix report.

https://drive.google.com/open?id=1XknisXvElS6uQN224bEcZ_biX7m-4el4

Any help would be appreciated :)

Upvotes: 1

Views: 521

Answers (2)

Alexis Olson
Alexis Olson

Reputation: 40204

The link that @MikeHoney gives has really useful information on the subtleties of relationships and does relate to this problem (do watch it!), but this issue is not ultimately related to bidirectional filtering in particular. In fact, I can reproduce it with this simplified relationship structure:

Relationship Diagram

The key thing to note here is that when you attach Table2 to Dates, since Table2 contains T2Date values that don't match to any Date[date], this creates an extra row in Dates with a blank date which you can notice in your filter on 6. Year when that relationship exists (active or inactive). Filtering out that that blank in the 6. Year filter would work, except that in your measure, you use ALL(Dates) to strip all filtering done on that table.

There are multiple ways to resolve this discrepancy, the easiest being replacing ALL with ALLNOBLANKROW. If you used ALLSELECTED that would also work in conjunction with filtering out blanks on your report-level filter on 6. Year.

Cleaning up some items not relevant in this context and changing ALL to ALLNOBLANKROW, your total measure can be more simply written as:

ALLNOBLANKROW =
VAR EndServiceDate =
    MAX ( Dates[Date] )
RETURN
    CALCULATE (
        SUM ( Table1[Net Amount] ),
        FILTER (
            ALLNOBLANKROW ( Dates ),
            Dates[Date] <= EndServiceDate
        ),
        Table1[Flag2] = 1,
        Table1[Flag] = TRUE ()
    )

Results with no 6. Year filter and with two measures, one using ALL and one using ALLNOBLANKROW:

Comparison Table

Notice that every row in the ALL column has been reduced by -7,872.01. This is the sum of all the Net Amount values that don't match to any dates in the Dates table. If you remove the relationship from Dates[date] to Table2[T2Date] then the blank row no longer exists and both of these will match the ALLNOBLANKROW version.

Upvotes: 3

Mike Honey
Mike Honey

Reputation: 15027

Setting the Cross Filter Direction to Both on any relationship is a bit risky - you essentially hand over control of the runtime query designs to the Power BI robots. There's then a risk that they will come up with a "creative" query design that is unexpected.

There's some insight into how this happens in a recent talk by Alberto Ferrari:

https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/

I'm sure you'll agree it's quite terrifying.

Looking at your info, I expect you can avoid those traps by changing the Cross Filter Direction to Single, for the relationship from MonthYear to Dates.

Upvotes: 2

Related Questions