Forum Discussion

rai024's avatar
rai024
Frequent Visitor
2 years ago
Solved

Adding a filter after removing filter context on the same column

Hi,

 

I am using Azure maps to show 3d columns on given latitudes and longitudes. I have a table that contains latitude, longitude, date,hour and value. I am showing value as a 3d column. I am using a slicer to select dates to see the data for only selected dates. I am also using the play axis to animate the value by hour. I have another requirement when I hover over a particular location (latitude and longitude) then I should be able to see the last 7 days of data from the latest date as tool tip, irrespective of the dates selected. I am implementing this using a measure with the following DAX formula.

SelectedValue =
CALCULATE(
    SUM('MIN_1069 (2)'[Value]),
    ALL(MIN_1069[Value.Date]),
    MIN_1069[Value.Date]>=MAX(MIN_1069[Value.Date])-7)
 
In my slicer, I have selected only the latest date. When I hover over any location I get data for the dates that I have selected in the slicer (in this case the latest date only). I get all dates when I remove MIN_1069[Value.Date]>=MAX(MIN_1069[Value.Date])-7 filter. Could someone help me with my objective and let me know how I should modify the Dax formula? Consider me a new to power BI.

 

Thanks

Ayush

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    2 years ago

    Hi, rai024 

     

    will it  be SUM('MIN_1069 (2)'[Value]) or  SUM('MIN_1069'[Value])?

     

    Also use this revised DAX see if it helps.

     

    SelectedValue =
    VAR MaxDate = CALCULATE(MAX(MIN_1069[Value.Date]), ALL(MIN_1069))
    RETURN
    CALCULATE(
    SUM(MIN_1069[Value]),
    ALL(MIN_1069[Value.Date]), -- remove any filter on the date column
    MIN_1069[Value.Date] >= MaxDate - 6,
    MIN_1069[Value.Date] <= MaxDate
    )

     

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    Hi, rai024 

     

    will it  be SUM('MIN_1069 (2)'[Value]) or  SUM('MIN_1069'[Value])?

     

    Also use this revised DAX see if it helps.

     

    SelectedValue =
    VAR MaxDate = CALCULATE(MAX(MIN_1069[Value.Date]), ALL(MIN_1069))
    RETURN
    CALCULATE(
    SUM(MIN_1069[Value]),
    ALL(MIN_1069[Value.Date]), -- remove any filter on the date column
    MIN_1069[Value.Date] >= MaxDate - 6,
    MIN_1069[Value.Date] <= MaxDate
    )

     

    • rai024's avatar
      rai024
      Frequent Visitor
      2 years ago

      Worked almost. I wanted the largest selected date so I removed the All from the variable and it worked. Thank you. I have been at this for a couple of days.

      • rubayatyasmin's avatar
        rubayatyasmin
        Community Champion
        2 years ago

        rai024 if my answer helped you then accept my answer as a solution. So that the thread is marked as completed. 

         

        Thanks.