DAX query to select values between two given date

Question

I am new in DAX query and trying to to get the distinct list of promotion_name from the promotion table between two dates which are from other two columns of that table. I have written the below DAX query for a measure. However, I am getting an error. I am sure the query is not correct. Can I get some help?

Measure = 
CALCULATE(VALUES(promotion[promotion_name]),
FILTER (ALL(promotion),
    promotion[start_date] >= DATE(1997,1,1) &&
    promotion[end_date] <= DATE(1997,12,31)))

Basically I want to implement the this SQL query in DAX:

select promotion_name
from promotion
where start_date >= '1998-01-01 00:00:00' AND
        end_date <= '1998-12-31 00:00:00'

Basically I want to implement the this SQL query in DAX: " Select promotion_name from promotion where start_date >= '1998-01-01 00:00:00' AND end_date <= '1998-12-31 00:00:00' " — Zahir
A measure only returns a single value, not a list or table. What are you trying to do with the distinct list? — Alexis Olson
Thanks for the reply. But how can I produce it in DAX, if it is possible in DAX as like we can do it in SQL. — Zahir

Marco Vos Marco Vos · Accepted Answer · 2018-11-10T09:17:45

You are nearly there. As stated by Alexis, this DAX expression returns a table and not a single value like a measure. So use CALCULATETABLE in stead of CALCULATE. The ALL function in the FILTER expression is not necessary here. So try this in New Table on the Modeling-tab:

Table =
CALCULATETABLE (
    VALUES ( promotion[promotion_name] ),
    FILTER (
        promotion,
        promotion[start_date] >= DATE ( 1997, 1, 1 )
            && promotion[end_date] <= DATE ( 1997, 12, 31 )
    )
)

Note that VALUES ( promotion[promotion_name] ) will return the distinct values. If you need all values with duplicates, you need to replace VALUES ( promotion[promotion_name] ) with SELECTCOLUMNS('promotion',"promotion_name",'promotion'[promotion_name]).

DAX query to select values between two given date

1 Answers