Forum Discussion

Arclite's avatar
Arclite
New Member
2 years ago

Removing Filter Context

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 

Dax Help
power bi

2 Replies

  • GuillaumePower's avatar
    GuillaumePower
    Resolver I
    2 years ago

    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.

  • Arclite's avatar
    Arclite
    New Member
    2 years ago

    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