Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
rai024
Frequent Visitor

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

1 ACCEPTED SOLUTION
rubayatyasmin
Super User
Super User

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
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


View solution in original post

3 REPLIES 3
rubayatyasmin
Super User
Super User

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
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

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

 

Thanks. 


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.