topic Re: Convert SQL Query with Variables to DAX Query in DAX Commands and Tips

Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 18:10:12 GMT

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!

Re: Convert SQL Query with Variables to DAX Query

HotChilli — Fri, 07 Feb 2020 00:00:44 GMT

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)?

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 01:16:14 GMT

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!

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 01:50:11 GMT

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.

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 17:29:32 GMT

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

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 18:11:09 GMT

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

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 18:34:31 GMT

I think you are using live connection.

Create date table in your tabular model only.

Thanks,
Pravin

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Fri, 07 Feb 2020 18:58:03 GMT

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!

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Sat, 08 Feb 2020 02:20:10 GMT

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

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Sat, 08 Feb 2020 03:59:29 GMT

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!

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Wed, 12 Feb 2020 14:34:37 GMT

No one to help me?

Re: Convert SQL Query with Variables to DAX Query

HotChilli — Wed, 12 Feb 2020 15:39:26 GMT

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

Re: Convert SQL Query with Variables to DAX Query

Anonymous — Wed, 12 Feb 2020 16:21:18 GMT

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!