Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Convert SQL Query with Variables to DAX Query

Could someone please help me in converting this working SQL Query to a DAX Query?   SELECT [ProductNumber], CASE WHEN @StartDate BETWEEN [StartDate] AND [EndDate] THEN @StartDate...
DAX
DAX Calculation
HotChilli's avatar
HotChilli
Community Champion
6 years ago

I think the original question has been made more complicated by the additional tables.  So i'll try and give a form for the DAX (you can develop it for your needs).

I simplified the logic in the original SQL case statements (because if there are two options and it's not one it must be the other)

 

EVALUATE
VAR _startDate = DATE(2020,01,14)
VAR _endDate = DATE(2020,01,11)
            
RETURN
    SELECTCOLUMNS(FILTER(Promos, Promos[StartDate] <= _enddate && Promos[EndDate] >= _startdate ),
          "ProductNumber", Promos[ProductNumber], 
          "StartDate", IF (_startDate < Promos[StartDate], Promos[StartDate], _startDate),
          "EndDate", IF (_endDate > Promos[EndDate], Promos[EndDate], _endDate),
          "PromoCode", Promos[PromoCode],
          "DaysAvailable", Promos[DaysAvailableInWeek] )

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi HotChilli , thanks for posting the query. Since it is a Star Schema, I have multiple tables - One Fact and Many Dimensions. So, if I specify only one table, it doesn't let me add other tables and that is where I am seeking the Community help as I am fairly new to DAX world. Any help with that would be highly appreciated. 

 

Thanks!