Pass a parameter to dax query

[Spyrosmak]

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

[Spyrosmak]

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]

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]

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 · Accepted solution]

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.

[SamWiseOwl]

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]

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

)