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

     

     

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)

      • Anonymous's avatar
        Anonymous
        Not applicable
        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.