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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Riktastic
Frequent Visitor

Ignoring visualisation field level filters

Hi Power BI folks,

 

I have been trying to solve this problem for a while. What if you wanted to not create a YTD line chart but a chart that starts at a custom period and ends at a custom period. For this use case I created a between slicer and added a line chart with months on the X-axis and this formula on the Y-axis:

 

VAR minDate = CALCULATE(MIN(D_Dates[Date]), ALL()) // Should ignore the X-axis filter
VAR currentDate = CALCULATE(MAX(D_Dates[Date])) // Should use the X-axis filter
VAR totalAmount = CALCULATE(SUM(F_SalesOrderLines[TotalAmount]), FILTER(ALL(D_Dates), D_Dates[Date]>=minDate && D_Dates[Date]<=currentDate))
RETURN totalAmount

 

The problem is: The minDate variable should be calculated while ignoring the filters of the X-axis.

In some situations I managed to get it working by creating a copy of the date table (D_Date_Alternative) which then is used as the X-axis. Which allows me to ignore the X-axis using the ALL() DAX function.

Is there a proper way to filter "visualisation field level filters"out? Without making my datamodel overly complex.

Kind regards,

 

Rik

2 REPLIES 2
Fowmy
Super User
Super User

@Riktastic 

I am not sure if I understand your question correctly. To create a cumulative sales based on a custom start and end date with a model where you have a single dates tables connected to a fact table, you follow this mesure. I have used ALL here so the the starting value is accumualted since the date when values are avilable, you can change it to ALLSELECTED to start the values to acumilate from the the minimum date. This ignores filters applied to the dates table frrom any place on your report.

**bleep** Sales = 
VAR __MinDate = CALCULATE( MIN('Calendar'[Date]) , all( 'Calendar' ) )
RETURN
CALCULATE(
    SUM(Sales[Value] ),
    'Calendar'[Date] >= __MinDate,
    'Calendar'[Date] <= MAX('Calendar'[Date])
)

 

Fowmy_0-1703680200298.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hi Fowmy, thank you for your reply. 
The problem that I'm having is that I want to filter on the values of the slicer. Ignoring any filters on the visual. 

 

For example: If I create a line chart and add months to the X-axis, the MIN(Date) and MAX(Date) of January will be 01/01/2023 and 31/01/2023. My slicer has been filtered to show values between  01/01/2022 and 01/01/2024. These are the values I want to access.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors