CODEWITHSUNDEEP
powerbidax
zam
zam

Reputation: 191

DAX query to select values between two given date

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'

Upvotes: 2

Views: 13295

Answers (1)

Marco Vos
Marco Vos

Reputation: 2967

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

Upvotes: 5

Related Questions