Adding a filter after removing filter context on the same column

[rai024]

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

[rubayatyasmin · Accepted solution]

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]

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]

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

Thanks.