Forum Discussion
How to return values based on if dates are within Slicer date range
Hello all :)
My user story: I would like to return values based on the following (in DAX)-->
- If the "dates.start" (a date I get from a query called "Tasks") is earlier than the right date on my date slicer AND
- If the "dates.due" (also from the same query) is after the left date on my data slicer
THEN --> return 0 else 1.
When I say "date slicer" I am refering to the visualization:
The field I am using for the data slicer comes from a separate calendar table ("CalendarTable") I created within PowerBI.
The formula I thought would work to meet the goals in the story above is:
CalculatedColumn = IF(AND(Tasks[dates.due]>FIRSTDATE(CalendarTable[Date]), Tasks[dates.start]<LASTDATE(CalendarTable[Date])), 0, 1)
The result with this code is that no matter how I set my date slicer, it always returns 0.
Thank you,
-L
Hi love
Calculated ccolumns do not react to slicer selections
May be you could try a MEASURE instead
MEASURE = IF ( AND ( SELECTEDVALUE ( Tasks[dates.due] ) > MIN ( CalendarTable[Date] ), SELECTEDVALUE ( Tasks[dates.start] ) < MAX ( CalendarTable[Date] ) ), 0, 1 )
3 Replies
- love
Helper I
/push
- Zubair_Muhammad
Community Champion
Hi love
Calculated ccolumns do not react to slicer selections
May be you could try a MEASURE instead
MEASURE = IF ( AND ( SELECTEDVALUE ( Tasks[dates.due] ) > MIN ( CalendarTable[Date] ), SELECTEDVALUE ( Tasks[dates.start] ) < MAX ( CalendarTable[Date] ) ), 0, 1 )- love
Helper I
Thank you very much Zubair_Muhammad, your solution worked perfectly. Also great info about the columns, thanks again!