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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JAlarco
Frequent Visitor

Make Meaure Ignore Filter and Sum by Date

Hi, 

 

I have a date table and I have a column that I wish to sum and ignore a page level filter for the date. There is a relative date filter set to the last day on the page. 

 

However, I would like to still display the sums by date in a line chart for 3 days, ignoring the relative date filter set for the previous day. Is this possible? 

 

The issue I am running into is that the CALCULATE(SUM(column), date_filter)) function sums the the column for the time period but the date associated with the sum is a single date. 

 

What I want is output to be is the sum by date in a line chart, so I would have:

 

2/19/23 4

2/18/23 5

2/17/23 6

In the form of a line chart with date along the x-axis and sum along the y-axis.

 

Instead, I am getting: 

 

2/19/23 15

 

(This is the sum of the column in the time period I filtered on in the CALCULATE function, but it sums the entirety for the period to a single day. 

 

Is there to have it display the sums of the column by day in a line chart rather than sum them completely?  

1 REPLY 1
JAlarco
Frequent Visitor

I found a solution, in case anyone ever sees this in the future. 

 

I have a model with several tables connected to one dim date table. 

 

Instead of using the dim date table for the chart or a calculate formula, I used the original date in the fact table and turned off cross-filtering and simply summed the column by date. With this, I was able to ignore the page level filter on the date. 

 

An alternative method would have been to use a slicer instead of a page level filter and disable interaction in the slicer format > edit interactions toolbar with the line chart. 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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