Forum Discussion

love's avatar
love
Icon for Helper I rankHelper I
8 years ago
Solved

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)--> 

  1. If the "dates.start" (a date I get from a query called "Tasks") is earlier than the right date on my date slicer AND
  2. 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

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity 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's avatar
        love
        Icon for Helper I rankHelper I

        Thank you very much Zubair_Muhammad, your solution worked perfectly. Also great info about the columns, thanks again!