slicer on fact table not affecting "running" calculated columns/measures

[dedelman_clng]

I have a fact table for "IT Outages" which joins to a date dimension table for "Date" on a single direction.  On this date dimension table I have made a number of calculated columns and measures to visualize the running total for "uptime" over the year (as an area graph).  I have also done measures to show the current YTD uptime as of the last refresh.

When I add a slicer and put in the "affected system" values from the fact table, the "single point in time" measures (e.g. YTD uptime as of today) correctly recalculate/display, but the graph visual is unchanged.

If I change the relationship to be a "both", then neither one visual works correctly (top right - YTD Outage Time).  I think this is because there is not an outage every day, so the dates that contibute to many of the columns/measures are excluded.  The measure that is correct only pulls from the fact table.

I can post my measure/columns if that would be helpful.

Thanks!

David Edelman

[v-shex-msft]

Hi @dedelman_clng,

Can you share us a sample file to test?

Regards,

Xiaoxin Sheng

[dedelman_clng]

A PBIX file can be found here:

Uptime Report with Slicer

[v-shex-msft]

Hi @dedelman_clng,

Based on test, I find the TOTALYTD function will be affect by filter, you should modify your measure which use the TOTALYTD function to ignore the filter, for example:

Before:
RunningUpTime = TOTALYTD(sum('Date'[UptimeMin]), 'Date'[Date])

After:
RunningUpTime = SUMX(Filter(all('Date'),[Date]>=Date(year(max([date])),1,1)&&[date]<=max([Date])),'Date'[UptimeMin])

Then modify the "cross filter direction" to both.

Regards,

Xiaoxin Sheng

[dedelman_clng]

I have tried your suggestion but the behavior is no different.  The graphs are still starting at and only taking into account those dates with a matching outage.

[dedelman_clng · Accepted solution]

After working on this for a while longer, I realized that I should have been using measures instead of calculated columns.  Once I changed the data used in the "values" of the line chart from a column to a measure, the filters worked correctly. 

The cross-filter join needs to be single-directional still in order for the graphs to pick up all of the values from the "Date" table.