Convert SQL Query with Variables to DAX Query

[Anonymous]

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
           WHEN @StartDate < [StartDate] THEN [StartDate]
       END AS [StartDate],
       CASE
           WHEN @EndDate BETWEEN [StartDate] AND [EndDate] THEN @EndDate
           WHEN @EndDate > [EndDate] THEN [EndDate]
       END AS [EndDate], 
       [PromoCode], 
       [DaysAvailableInWeek]
FROM [#Promos]
WHERE [StartDate] <= @EndDate
  AND [EndDate]   >= @StartDate
ORDER BY 1;

DDL:

CREATE TABLE [#Promos]
([ProductNumber]       INT, 
 [StartDate]           DATE, 
 [EndDate]             DATE, 
 [PromoCode]           VARCHAR(6), 
 [DaysAvailableInWeek] VARCHAR(20)
);

DML:

INSERT INTO [#Promos] ([ProductNumber], [StartDate], [EndDate], [PromoCode], [DaysAvailableInWeek])
VALUES (6512, '2020-01-11', '2020-01-13', 'ABC123', '1, 3, 5');
INSERT INTO [#Promos] ([ProductNumber], [StartDate], [EndDate], [PromoCode], [DaysAvailableInWeek])
VALUES(6514, '2020-01-12', '2020-01-14', 'ABC123', '4, 6');
INSERT INTO [#Promos] ([ProductNumber], [StartDate], [EndDate], [PromoCode], [DaysAvailableInWeek])
VALUES(2341, '2020-01-11', '2020-01-25', '321XYZ', '1, 2');

Here is what I tried so far, but it doesn't return correct results. 

EVALUATE SUMMARIZECOLUMNS(
'Dim_Promos'[PromoCode], 
'Dim_Product'[ProductNumber], 
'Dim_Date_Start'[FullDate], 
'Dim_Date_End'[FullDate], 
FILTER('Dim_Date_Start', 'Dim_Date_Start'[FullDate] <= DATE(2020, 01, 15) ), 
FILTER('Dim_Date_End', 'Dim_Date_End'[FullDate] >= DATE(2019, 04, 11) ),
"Days Available In Week", [Days Available In Week])

Also, I would need the date formatted as YYYY-MM-DD. When I tried the FORMAT function, it is throwing syntax error.

FORMAT('Dim_Date_Start'[FullDate], "YYYY-MM-DD"), 
FORMAT('Dim_Date_End'[FullDate], "YYYY-MM-DD")

Thanks!

[HotChilli]

Help us out please.

Can you provide some sample data (not a picture) please?

Also, how do you assign the two variables (from the data by row or passed from slicers)?

[Anonymous]

@HotChilli  I've updated my post with some sample data I'm using. I'm trying to call the DAX Query from SSRS Report/Power BI where I pass the Start and End dates to filter the data I need. Let me know if you need any other information to help me.

Thanks!

[Anonymous]

First load you table in power bi. If you are using directquery just connect it to database.
Then create one custom date table in power bi which is not linked with any other table.

Calender function is there to create date table.

I assume you are using between slicer for start data and end date.

Add date column from date table into slicer.

Note as you mention
You want columns dynamic so it is not possible because columns get loaded at first load only and they can not updated with slicers.

So create measure
Startdate= if(max(table[startdate]) <= min(date[date])&& max(table[Enddate])>= min(date[date]),max(table[startdate]),if(min(date[date])<=max(table[startdate]),max(table[startdate])

Similarly create one more meausre for end date.
Just you need to replace min(date[date]) with max(date[date]) and startdate with enddate column.

For where clause create one conditional flag.

Flag=If(max(table[startdate]) < max(date[date]) && max(table[enddate]) > min(date[date]),1,0)

Add this measure to visual level filter and set it to 1.

Thanks,
Pravin

If it resolves your problem mark it as a solution and give Kudos.

[Anonymous]

I am not getting an option to create a new data table when connected to my source database. That's greyed out.

[Anonymous]

I've updated my original post with what I've tried so far. Could someone please help me from here at least?

[Anonymous]

I think you are using live connection.

Create date table in your tabular model only.

Thanks,
Pravin

[Anonymous]

I don't think I have privileges to create in Tabular Model. Is it not possible without that? I've written most of query in DAX except that I am struggling with the filters as mentioned in my SQL example. Would you be able to help at least with that part please?

Thanks!

[Anonymous]

Any how you need date dimension.
Ask tabular model creator to create this data dimension for you in model and tell him to do not link it with any other table.

Then you can access it from power bi side and follow steps which i have already mentioned.

Thanks,
Pravin

[Anonymous]

Hi @Anonymous, I am not sure if you've seen my last post. I've updated my original post with the DAX Query I've built so far which already includes a Date Dimension. I am not an expert in DAX and would like someone to help me with convert my working SQL Query into a complete DAX Query (which I've already built partially).

Thanks!

[Anonymous]

No one to help me?

[HotChilli]

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]

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!