Forum Discussion

nleuck_101's avatar
nleuck_101
Icon for Continued Contributor rankContinued Contributor
1 year ago

Measure ignore interaction with another visual

Hello All,

 

I'm not sure if this is even possible but I figured I would reach out to the community for help. I have two measures one for total views and the other for last 2 wks % change. I have these displayed in a table. I also have a line chart that if you select the day it changes both measures. I would only want it to change the first measure total views. Is this possible? Any help would be greatly appreciated!

No date selected in line chart:

Date selected in line chart ( I don't want my % measure to change):

 

Need Help

7 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Yes, you can prevent the percentage change measure from being affected by the selection in the line chart while still allowing the total views measure to change.

    Solution: Use ALLSELECTED or REMOVEFILTERS

    Modify your % Change Measure to ignore the filter coming from the line chart:

    Updated DAX Measure for % Change

    % Change =
VAR PrevPeriod =
    CALCULATE(
        SUM('Table'[Views]),
        DATEADD('Table'[Date], -14, DAY),
        REMOVEFILTERS('Table'[Date])  -- Ignores date selection
    )
VAR CurrentPeriod =
    CALCULATE(
        SUM('Table'[Views]),
        REMOVEFILTERS('Table'[Date])  -- Ignores date selection
    )
RETURN
    IF(PrevPeriod = 0, BLANK(), (CurrentPeriod - PrevPeriod) / PrevPeriod)

     

    • nleuck_101's avatar
      nleuck_101
      Icon for Continued Contributor rankContinued Contributor
      1 year ago

      rohit1991 

      One quick note, if I select a date that is more than 2wks the calculation doesn't work. It changes the calculation.
      Date selected more than 2 wks ago.

       

      • rohit1991's avatar
        rohit1991
        Icon for Super User rankSuper User
        1 year ago

        Hi nleuck_101 ,

         

        Great question! You can definitely set it up so that your % change measure ignores the date selection from your line chart, while your total views measure still responds to it.


        Just use REMOVEFILTERS or ALL on the date column inside your % change measure. This tells Power BI to always calculate the last two weeks based on the latest available date in your data, no matter what’s selected in the line chart.

         

        % Change =
VAR LastAvailableDate =
    CALCULATE(
        MAX('Table'[Date]),
        REMOVEFILTERS('Table'[Date])
    )
VAR PrevPeriod =
    CALCULATE(
        SUM('Table'[Views]),
        DATESINPERIOD('Table'[Date], LastAvailableDate, -14, DAY),
        REMOVEFILTERS('Table'[Date])
    )
VAR CurrentPeriod =
    CALCULATE(
        SUM('Table'[Views]),
        DATESINPERIOD('Table'[Date], LastAvailableDate, 0, DAY),
        REMOVEFILTERS('Table'[Date])
    )
RETURN
    IF(PrevPeriod = 0, BLANK(), (CurrentPeriod - PrevPeriod) / PrevPeriod)

         

        Your Total Views measure can remain as it is, so it’ll still react to date selections on your line chart. For the % change, using REMOVEFILTERS('Table'[Date]) ensures it always gives you the change over the latest two weeks, regardless of what’s selected elsewhere.

         

         

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi nleuck_101 ,

    This is achieved by using the ALL or REMOVEFILTERS functions in DAX, which allow you to modify how filters affect the calculation. In your case, you want the total views measure to respond to the interaction from the line chart but ensure that the last 2 weeks % change measure ignores the interaction.

    Can you share your DAX measures used?

  • nleuck_101's avatar
    nleuck_101
    Icon for Continued Contributor rankContinued Contributor
    1 year ago

    Bibiano_Geraldo 

    I tried using ALL and it changed the calculation completely. I'm guessing it removed my current date filters in the measure. Below is my measure.