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
varshajain
Microsoft Employee
Microsoft Employee

Relative date slicer not working in DAX

I have a Date slicer which is powered by Candidates table. I have another filter Stage also powered by Candidates table. If I select any option in Environment filter, I wish to take all the records from Candidates table for the given date range selected irrespective of Environemnt filter  and then perform further filtration depending on the option I selected from the Environment slicer.

I have this DAX formula so far but it doesn't seem to work. 

 

Result=
IF(
    SELECTEDVALUE(Candidates[Environment])=="Prod","This works fine",  
    IF(
        SELECTEDVALUE(Candidates[Environment])=="Pilot",
         CALCULATE(SUM(Candidates[TP]),
                    FILTER(ALL(Candidates),  Candidates[CreateDate] >= MINX(ALLSELECTED(Candidates[CreateDate]), Candidates[CreateDate].[Date]) && Candidates[CreateDate] <= MAXX(ALLSELECTED(Candidates[CreateDate]), Candidates[CreateDate].[Date]) &&  Candidates[Environment]== "Prod"
)),"NA" ))
 
Could someone help me with this and let me know what is it that I am doing wrong?
1 ACCEPTED SOLUTION
hymie
New Member

FILTER(ALL(Candidates) means include the entire Candidate table. So the following MINX includes all rows too. You need to create two variables at the top of the expresion to capture the min and max ALLSELECTED values, then use those variables in the FILTER.

View solution in original post

2 REPLIES 2
hymie
New Member

FILTER(ALL(Candidates) means include the entire Candidate table. So the following MINX includes all rows too. You need to create two variables at the top of the expresion to capture the min and max ALLSELECTED values, then use those variables in the FILTER.

varshajain
Microsoft Employee
Microsoft Employee

Thank You! I added this and it worked

var _max = maxx(allselected('Candidates'[CreateDate]),'Candidates'[CreateDate])
var _min = minx(allselected('Candidates'[CreateDate]),'Candidates'[CreateDate])

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

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!