Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlB
Super User
Super User

Weird behavior after creating relationship

Hi all,

See attached file.

We have a simplified date table DateT, Table1 and this measure:

 

Measure = 
VAR aux_ = MAX ( 'DateT'[Date] )
VAR t_ =
    CONCATENATEX (
        CALCULATETABLE ( DISTINCT ( 'DateT'[Year] ), 'DateT'[Date] <= aux_ ),
        'DateT'[Year],
        ", "
    )
RETURN
    t_

 

 With no relationship between the tables, if we place DateT[Year] and [Measure] in a table visual we obtain the expected result:

image.png

However, if we create a 1-to-many relationship between DateT[Date] and Table1[Date] we obtain the result below. This, in my view, does not make any sense. The relationship should not affect the result at all, let alone give rise to this result. If the relationship is inactive, we also obtain the result below. Does anyone have any idea what is going on?

 

image.png

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

I think this article explains what you are seeing.

Mark as Date table - SQLBI

 

Here is the relevant snippet

In order to simplify the usage of time intelligence functions, the DAX engine makes an assumption when two tables are related through a column of Date data type: When a filter is applied on the key of the relationship – Date[Date] in this example – the new filter overrides any other filter on the Date table. It basically applies a REMOVEFILTERS ( Date ) to the filter context every time you apply a filter on the Date[Date] column. This behavior occurs automatically only when the relationship is based on a column of Date data type.

You can obtain the same behavior – that is, adding REMOVEFILTERS on the table whenever a new filter is applied on the Date column – by marking the table as a date table. When you mark a table as a date table, Power BI asks which column contains the dates of the calendar. This is required because the engine adds REMOVEFILTERS every time you apply a filter on that specific column.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hey,

 

late to the party, but nevertheless I want to share the link to one of my favorite DAX articles. This one has been written by Jeffrey Wang some time ago:

MDX and DAX topics: DAX Time Intelligence Functions (mdxdax.blogspot.com)

 

As @mahoneypat already mentioned the behavior is related to the internal workings of related date columns. Here is the relevant snippet from this article:

If a Calculate filter has a unique column that is of data type date/time, all previous filters on all columns from the table which contains this date/time column are removed.

 I still read this article, whenever I'm facing some more complex DAX challenges where the calendar plays its part, sometimes in preparation, but most of the time to fix things 🙂

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

11 REPLIES 11
mahoneypat
Employee
Employee

I think this article explains what you are seeing.

Mark as Date table - SQLBI

 

Here is the relevant snippet

In order to simplify the usage of time intelligence functions, the DAX engine makes an assumption when two tables are related through a column of Date data type: When a filter is applied on the key of the relationship – Date[Date] in this example – the new filter overrides any other filter on the Date table. It basically applies a REMOVEFILTERS ( Date ) to the filter context every time you apply a filter on the Date[Date] column. This behavior occurs automatically only when the relationship is based on a column of Date data type.

You can obtain the same behavior – that is, adding REMOVEFILTERS on the table whenever a new filter is applied on the Date column – by marking the table as a date table. When you mark a table as a date table, Power BI asks which column contains the dates of the calendar. This is required because the engine adds REMOVEFILTERS every time you apply a filter on that specific column.

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

Great. Thank you. That's exactly what I was looking for. It's always these under-the-hood automatic things with dates 🙄  Even if you don't want to use time intelligence functions...

@MFelix 

Thanks for your reply. I believe the article @mahoneypat pointed out describes exactly what is going on here.

 

aj1973
Community Champion
Community Champion

Hi,

I had this exact same behavior a year ago with one of my report... indeed it was weired

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

AllisonKennedy
Super User
Super User

@AlB Interesting example and challenge. Here are my rambling thoughts:

 

I can't find the official Microsoft doc on it anymore, but inactive relationships do impact DAX sometimes, as detailed in this solution here: https://community.powerbi.com/t5/Desktop/Do-inactive-relationships-affect-DAX/td-p/1092904 

 

If you mark your DateT table as a date table, you get the 2nd screenshot you posted with or without the relationship. 

 

Your measure also has no reference to Table1, so this is irrelevant. 

 

I think what is happening is that marking DateT as a date table or putting it on the 1 side of a relationship tells Power BI that the date column is unique, which changes the behaviour. 

 

Try this measure for example, where we use the [Year] column instead of [Date] to filter your values, since you have used DISTINCT(Year) in your calculatetable, this behaves as the second screenshot in all scenarios: 

 

Measure =
VAR aux_ = MAX ( 'DateT'[Date] )
VAR year_= MAX(DateT[Year])
VAR t_ =
CONCATENATEX (
CALCULATETABLE ( DISTINCT ( 'DateT'[Year] ),
-- DateT[Date]<=aux_
'DateT'[Year] <= year_
),
'DateT'[Year],
", "
)
RETURN
t_
 
 
I would expect the second screenshot as the 'expected result' since you're using CALCULATETABLE to modify the filter context. 

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy 

Many thanks for your reply

1. For me the expected result is clearly the first screenshot. Within CALCULATETABLE, we are clearing the DateT[Date] column only, so the filter context still has DateT[Year] and that should prevent all other years (other than the one in the current row of the visual) to show up. 

2. It is true that marking it as date table produces the second screenshot in both scenarios (with or without relationship). That is interesting. Why would that happen? Why does the result change when marking it as date table? So in that case the result would be the unexpected one in both scenarios.

3. Even if with a 1-to-many relationship we are indicating that the column on DateT is unique, what difference would that make? Why would that change the behaviour? I don't think that should have an effect at all in this case

4. The alternative measure you provide yields what I would expect in both cases, since you are clearing the Year column within CALCULATETABLE and we have only Year in the filter context. But that is not equivalent to what we are doing in the first measure so not completely relevant. I'm interested in knowing what is going on with the first measure.

 

@aj1973 

Thanks for your comment

 

@AlB  Your post is much more organised than my ramblings. I'll try to be a bit more logical with this reply. 

 

1. According to Microsoft Docs on CALCULATE table; 

When filter expressions are provided, the CALCULATETABLE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:

  • If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
  • If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATETABLE expression.

Even though you are referencing a different column (Date not Year), the table is the same, so the filter expression in your calculate table overwrites the current filter context. 

 

This link explains it a bit better: https://dax.guide/calculatetable/

 

AllisonKennedy_0-1609473119641.png

To get your expected result, you need to add in a KEEPFILTERS: 

 

Measure =
VAR aux_ = MAX ( 'DateT'[Date] )
VAR year_= MAX(DateT[Year])
VAR t_ =
CONCATENATEx (
CALCULATETABLE ( DISTINCT ( 'DateT'[Year] ),
KEEPFILTERS( DateT[Date]<=aux_ )
),
'DateT'[Year]
, ", "
)
RETURN
t_

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy 

Many thanks for your reply. These pieces of text seem to reinforce my opinion. On the first 1, the relevant part is 

If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.

That simply supports what I am saying

The second one also states that the filter expression overrides the corresponding filters over the same column(s).

Again, it corroborates my view.

I am attaching another example that is very similar to the original one in structure but shows exactly the behaviour I expect, the standard one in my view. Both with and without relationships.

In any case, the question still remains as to why creating the relationship in the first file causes the change in behaviour in the measure.

I'll bring in some other people to hear their thoughts

@TomMartens     @MFelix  @mahoneypat   @lbendlin  @edhans 

Thank you

Hey,

 

late to the party, but nevertheless I want to share the link to one of my favorite DAX articles. This one has been written by Jeffrey Wang some time ago:

MDX and DAX topics: DAX Time Intelligence Functions (mdxdax.blogspot.com)

 

As @mahoneypat already mentioned the behavior is related to the internal workings of related date columns. Here is the relevant snippet from this article:

If a Calculate filter has a unique column that is of data type date/time, all previous filters on all columns from the table which contains this date/time column are removed.

 I still read this article, whenever I'm facing some more complex DAX challenges where the calendar plays its part, sometimes in preparation, but most of the time to fix things 🙂

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

@TomMartens 

Very interesting article. Thanks very much

 

My pleasure!



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @AlB ,

 

Believe that this has to do with the way CALCULATEDTABLE works has the USERELATIONSHIP syntax so when you add the inactive relationship in fact you are making the relationship active by the use of the CALCULATEDTABLE.

 

I have to check a little bit better some information but this is explained in the SQLBI article USERELATIONSHIP in Calculated Columns .

 

The problem with CALCULATE (CALCULATEDTABLE) on the context transiction is very big, so the use of calculate with filters and relationship is not always easy to grasp.

 

Also check the blog post that are referenced in the DAX Guide .

 

Hope this helps.

 

What you can do is to replace your measure to:

Measure = 
VAR aux_ = MAX ( 'DateT'[Date] )
VAR t_ = 
    CONCATENATEX (
        DISTINCT(  'DateT'[Year] ),
        DateT[Year],
        ", "
    )
RETURN
    t_ 

 

This way it will work with or without relationship.

MFelix_0-1609598933697.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.