topic Removing Filter Context in DAX Commands and Tips

Removing Filter Context

Arclite — Thu, 11 Jul 2024 16:27:35 GMT

Hi,

I really need help with, what I think is a DAX issue.

I have on my report page, two tables. Each table displays my companies top 25 energy-using facilities and their respective correlation cofficients x=date and y= SEC (kWh/ML) - SEC is a measure.

The page has one filter, which is Year/Month, which is a calculated column - FORMAT('Calendar'[Date], "YYYY-MM")

The first table dynamically displays the correlation by the chosen Year/Month (so, taking into account only the SEC values for that given Year/Month) and the second table calculates the correlation by the chosen Year/Month, but also takes into account the previous two months. This is essentially a rolling 3-months. I have the correlation measure providing the correct output for the second table, but where I become unstuck, is the custom tooltip for the second table.

I have a custom tooltip page simply displaying SEC(kWh/ML) in the y-axis and the Date in the x-axis. This isn't a problem for the first table as it only shows the correlation by month, but for the second table I need the custom tooltip line chart to show 3-months (current Year/Month selection and two months prior).

I am trying to drive both tables by the same slicer (Year/Month), despite having two different granularities, which I think is causing the issue. The slicer is imposing the 1-month view on the 3-monthly set up of the second table. I have tried all sorts of combinations to remove the filter context, so the custom tooltip line chart will display the current Year/Month and the two previous months, but with no success. I'm hoping some clever person can help resolve this.

Many thanks

Re: Removing Filter Context

GuillaumePower — Thu, 11 Jul 2024 16:06:20 GMT

Hey, could you please, send the graph (you copy paste in two seconds your screen here) and your Dax measure? I could say that it could be usefull to use removefillter() in your calculate() but if I don't see your measure this doesn't help.

Re: Removing Filter Context

Arclite — Wed, 17 Jul 2024 10:35:15 GMT

Hi,

Sorry for delayed reply. Here is the measure, I'm using this measure in the y-axis of the custom tooltip and date from my calendar table in the x-axis

SEC 3-Month Rolling =
VAR SelectedMonthYear = SELECTEDVALUE('Calendar'[MonthYear])
VAR SelectedYear = VALUE(LEFT(SelectedMonthYear, 4))
VAR SelectedMonth = VALUE(RIGHT(SelectedMonthYear, 2))
VAR CurrentDate = DATE(SelectedYear, SelectedMonth, 1)

-- Calculate start and end dates for the 3-month period
VAR EndDate = EOMONTH(CurrentDate, 0)
VAR StartDate = EOMONTH(CurrentDate, -2) + 1

-- Calculate SEC (kWh/ML) for the date range
RETURN
CALCULATE(
[SEC (kWh/ML)], -- Use the existing measure
REMOVEFILTERS('Calendar'[MonthYear]), -- Remove the MonthYear filter context
DATESBETWEEN(
'Calendar'[Date],
StartDate,
EndDate
)
)

What you can see in the image (redacted), is a table with 25 facility names and their respective correlation coefficients based on Year-Month slicer (visible). The custom tooltip works perfectly in the context of the 1-month view, as its being governed by the YearMonth slicer. However, I have a toggle (also visible) where I switch a second table that shows the same 25 facilities but, this time with correlation coefficients for the 3-months (the chosen YearMonth and 2 months prior). Despite, what I have tried, I can't seem to show 3-months of data in the custom tooltip. You can clearly see the line is flat, as the measure isn't working. In a previous iteration of the report, I had 2 separate slicers, each controlling their respective table, but I have been asked by a stakeholder specifically to have only 1 slicer on the page. Any help would be much appreciated. Thanks