Forum Discussion
Time Intelligence - Data Table Relationship with CALENDAR date not working
Hey AB,
each measure is evaluated in the so called Filter Context. The Filter Context filters down the table used in the expression, in your example this is the table KPI2_... This means before the expression is evaluated the current Current Filter context is applied to the table. The Filter Context is determined by the selections made from the slicers and also by the row and column headers. After the Filter Context is applied the expression is evaluated to the remaining rows, this explains why we always (at least most of the time have to aggregate functions like SUM('KPI2_...'[PlannedCosts]). This becomes different if we use iterator funtions like SUMX() and the lot. To me it seems, that most measures I calculate require that this Filter Context has to be adjusted. This adjustment is possible if we us CALCULATE(<expression>, FITLER1,...FILTERn).
If you use the Date column from your Calendar table something called Filter Propagation happens. The current selection from the Calendar table is propagated to the many side of an existing relationship.
The working linkage of your Calendar table and your fact table 'KPI2_...' is demonstrated by the 1st table of your screenshot.
If you use the Date column from the table 'KPI2_...' this selection is used to filter this table and the [PlannedCosts] column in your first post shows the correct value (the 2nd table in your screenshot).
If you use the Date column from the table 'KPI2_..." in your table visual, there is no filter on the Calendar table for this reason the FILTER(...) will always return the same table containing the last 7 seven days of your Calendar table. This table is then used to Filter down your 'KPI2_' table. I assume that there are no values available in your table 'KPI2_...' for these last 7 days, for this reason no value is shown for your measure LASTWEEK_PLAN_COSTS
You may reconsider using the Calendar table for your visuals from my experience this is a common practice, if you won't or can't then you have to rewrite your measure to use your Date column from your table 'KPI2_...'.
In addition to Matt Allington's book I also recommend the following books from Marco Russo and Alberto Ferrari
- Analyzing Data with Microsoft Power BI and Power Pivot for Excel
- The Definitive Guide to DAX
Regards
Thanks Tom, you make perfect sense and thankyou for taking the time to make the detailed explanation :-) (BTW I have also purchased a copy of Marco & Alberto's "The Definitive Guide to DAX"
Regards
AB
Hi Tom
Many thanks for the prompt response and your question is gratefully received....
The Calendar table was built by me to allow the full use of the Time Intelligence ability in DAX as described in the Book "Learn to Write DAX" by Matt Allington. The purpose of this Calendar table is to support very flexible Date Arithmetic for things like dynamically calculating the difference from last week to this week using the Calendar Unique ID functionality (see Cal_ID in example measure beow). I don't believe that the Calendar table is intended to form an integral part of Power BI graphical solutions & reporting other than to act on the data as part of calculated measures as per this live example:
LASTWEEK_PLAN_COSTS = CALCULATE(
SUM(KPI2_RM_SC_WOBacklog_History_Stats[PlannedCosts]),
FILTER(
ALL('CALENDAR'),
'CALENDAR'[Cal_ID] >= MAX('CALENDAR'[Cal_ID]) - 7 &&
'CALENDAR'[Cal_ID] < MAX('CALENDAR'[Cal_ID])
)
)
The problem is:
I want to be able to use the date field in my fact table.
I have everything right (I hope) except the linkage between the fields which appears not to work as explained and this is worrying as it builds an element of "unknown" into my Dashboards that MIGHT hide other as yet unknown issues?
There are a few other posts about this same issue which I have read and acted on but without a resolution yet...
Regards
AB
Hey AB,
each measure is evaluated in the so called Filter Context. The Filter Context filters down the table used in the expression, in your example this is the table KPI2_... This means before the expression is evaluated the current Current Filter context is applied to the table. The Filter Context is determined by the selections made from the slicers and also by the row and column headers. After the Filter Context is applied the expression is evaluated to the remaining rows, this explains why we always (at least most of the time have to aggregate functions like SUM('KPI2_...'[PlannedCosts]). This becomes different if we use iterator funtions like SUMX() and the lot. To me it seems, that most measures I calculate require that this Filter Context has to be adjusted. This adjustment is possible if we us CALCULATE(<expression>, FITLER1,...FILTERn).
If you use the Date column from your Calendar table something called Filter Propagation happens. The current selection from the Calendar table is propagated to the many side of an existing relationship.
The working linkage of your Calendar table and your fact table 'KPI2_...' is demonstrated by the 1st table of your screenshot.
If you use the Date column from the table 'KPI2_...' this selection is used to filter this table and the [PlannedCosts] column in your first post shows the correct value (the 2nd table in your screenshot).
If you use the Date column from the table 'KPI2_..." in your table visual, there is no filter on the Calendar table for this reason the FILTER(...) will always return the same table containing the last 7 seven days of your Calendar table. This table is then used to Filter down your 'KPI2_' table. I assume that there are no values available in your table 'KPI2_...' for these last 7 days, for this reason no value is shown for your measure LASTWEEK_PLAN_COSTS
You may reconsider using the Calendar table for your visuals from my experience this is a common practice, if you won't or can't then you have to rewrite your measure to use your Date column from your table 'KPI2_...'.
In addition to Matt Allington's book I also recommend the following books from Marco Russo and Alberto Ferrari
- Analyzing Data with Microsoft Power BI and Power Pivot for Excel
- The Definitive Guide to DAX
Regards
Thanks Tom, you make perfect sense and thankyou for taking the time to make the detailed explanation :-) (BTW I have also purchased a copy of Marco & Alberto's "The Definitive Guide to DAX"
Regards
AB
Your welcome, it has been a pleasure.