Forum Discussion
Weird behavior after creating relationship
I think this article explains what you are seeing.
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
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
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/
To get your expected result, you need to add in a KEEPFILTERS:
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
My pleasure!
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.
