Forum Discussion
Community ChampionWeird 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:
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?
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
11 Replies
- mahoneypat
Microsoft Employee
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
- AlB
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...
Thanks for your reply. I believe the article mahoneypat pointed out describes exactly what is going on here.
- AllisonKennedy
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],", ")RETURNt_I would expect the second screenshot as the 'expected result' since you're using CALCULATETABLE to modify the filter context.- AlB
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.
Thanks for your comment
- AllisonKennedy
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:
Measure =VAR aux_ = MAX ( 'DateT'[Date] )VAR year_= MAX(DateT[Year])VAR t_ =CONCATENATEx (CALCULATETABLE ( DISTINCT ( 'DateT'[Year] ),KEEPFILTERS( DateT[Date]<=aux_ )),'DateT'[Year], ", ")RETURNt_
- aj1973
Hi,
I had this exact same behavior a year ago with one of my report... indeed it was weired
