Forum Discussion

Spyrosmak's avatar
Spyrosmak
Frequent Visitor
1 year ago
Solved

Pass a parameter to dax query

Hello all,

 

I have filter in my table with a dax query:

 

IsInDateRangeOrPending(weekly) =
IF(
    ('Main Query'[date] >= DATE(YEAR(TODAY()), 1, 1) &&
     'Main Query'[date] <= TODAY() ||
    'Main Query'[column1] = "Pending",
    1,
    0
)

This filter does the trick however, I would to be able to interact with DATE(YEAR(TODAY()), 1, 1)  and Today() from the frontend (with slicer or something). I have tried creating a date table and measures with startdate= min(date_table(date)) and stopdate = max(date_table(date)) but it didn't work and I cannot put a relationship from my new table to the old one because of duplicate dates issue. Does anyone know how instead of Today() I can have a variable that I can change dynamically with a visual(e.g. slicer ) for example?

Thank you
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Spyrosmak 

    You can try the follwing solution.

    the following is my test result.

    Sample main table.

     

    Calendar table.

    Create the following measure 

    IsInDateRangeOrPending(weekly) =
IF (
    SELECTEDVALUE ( 'Main Query'[date] ) >= FIRSTDATE ( Date_Table_end[Date] )
        && OR (
            SELECTEDVALUE ( 'Main Query'[date] ) <= LASTDATE ( Date_Table_end[Date] ),
            SELECTEDVALUE ( 'Main Query'[column1] ) = "Pending"
        ),
    1,
    0
)

    Then put the measure to the visual filter.

     

    Then put the date of clendar table to  a slicer, then it can work.

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

6 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    Hi Spyrosmak 

    This issue can be caused due to MIN not treating the returned value as a date.

    Try using FirstDate() and LastDate() instead.

     

    Is this trying to make a calculated column or a measure used in a table visual?

    • Spyrosmak's avatar
      Spyrosmak
      Frequent Visitor
      1 year ago

      Hello,

      I have tried this

      IsInDateRangeOrPending(weekly) =
      IF(
          ('Main Query'[date] >= LASTDATE(Date_Table_end[Date])
       &&
           'Main Query'[date] <= FIRSTDATE(Date_Table_end[Date])
       ||
          'Main Query'[column1] = "Pending",
          1,
          0
      )
      but it produces different numbers on my table

      than this
      IsInDateRangeOrPending(weekly) =
      IF(
          ('Main Query'[date] >= DATE(YEAR(TODAY()), 1, 1) &&
           'Main Query'[date] <= TODAY() ||
          'Main Query'[column1] = "Pending",
          1,
          0
      )
  • Spyrosmak's avatar
    Spyrosmak
    Frequent Visitor
    1 year ago

    Hello,

    I have tried this

    IsInDateRangeOrPending(weekly) =
    IF(
        ('Main Query'[date] >= LASTDATE(Date_Table_end[Date])
     &&
         'Main Query'[date] <= FIRSTDATE(Date_Table_end[Date])
     ||
        'Main Query'[column1] = "Pending",
        1,
        0
    )
    but it produces different numbers on my table

    than this
    IsInDateRangeOrPending(weekly) =
    IF(
        ('Main Query'[date] >= DATE(YEAR(TODAY()), 1, 1) &&
         'Main Query'[date] <= TODAY() ||
        'Main Query'[column1] = "Pending",
        1,
        0
    )


    • SamWiseOwl's avatar
      SamWiseOwl
      Super User
      1 year ago
      IsInDateRangeOrPending(weekly) =
      IF(
          ('Main Query'[date] >= LASTDATE(Date_Table_end[Date])
       &&
           'Main Query'[date] <= FIRSTDATE(Date_Table_end[Date])
       ||
          'Main Query'[column1] = "Pending",
          1,
          0
      )
      I don't know your data but intuatively should FIRST be >= and LAST <= they feel the wrong way around.
      • Spyrosmak's avatar
        Spyrosmak
        Frequent Visitor
        1 year ago

        Hello yes you are right, I changed the order however now the filter doesn't work at all. It changes the dates effectively but it doesn't affect my table. (the date table doesn't have relationship with the main table)