topic Re: What's the DAX syntax equivalent to a sql windowing in DAX Commands and Tips

What's the DAX syntax equivalent to a sql windowing

franck_axires — Fri, 06 Sep 2019 20:26:35 GMT

Hi all,

I'm stuck trying to figure out the DAX formula to solve this problem :

I need to filter a table based on the latest StatusCode at a given date.

Here's a sample of my Positions table :

PositionId	PositionDate	ContainerId	StatusCode
34408	08/03/2018	1	RCVE
95676	10/04/2018	1	RCVF
100525	12/04/2018	1	SUBL
101685	21/05/2018	1	RTSL
111024	23/05/2018	1	RCVF
585046	15/01/2019	2	RCVE
595659	22/01/2019	2	SUBL
622710	06/02/2019	2	RTSL
628154	08/02/2019	2	DVSF
255695	05/07/2018	3	TRLE
294286	26/07/2018	3	SUBL
341214	22/08/2018	3	RTSL
355613	30/08/2018	3	OTSH
89795	06/04/2018	4	DVSE
95552	10/04/2018	4	OTSH
97635	11/04/2018	4	SUBL
79650	26/03/2018	5	RCVE
79657	27/03/2018	5	SUBL
202818	05/06/2018	5	RTSL
202819	06/06/2018	5	TRLE
64422	22/03/2018	6	RCVF
193698	27/05/2018	6	SUBL
196336	03/06/2018	6	LVSF
206919	07/06/2018	6	DVSF

I need to find all the ContainerIds with a StatusCode of SUBL up to a certain date. For instance :

- on 01/04/2018, the results should be :

PositionId	PositionDate	ContainerId	StatusCode
79657	27/03/2018	5	SUBL

- on 01/05/2018, I should find :

PositionId	PositionDate	ContainerId	StatusCode
79657	27/03/2018	5	SUBL
97635	11/04/2018	4	SUBL
100525	12/04/2018	1	SUBL

- and on 01/02/2019 I should find

PositionId	PositionDate	ContainerId	StatusCode
79657	27/03/2018	5	SUBL
97635	11/04/2018	4	SUBL
100525	12/04/2018	1	SUBL
193698	27/05/2018	6	SUBL
294286	26/07/2018	3	SUBL
595659	22/01/2019	2	SUBL

It seems to me to be the equivalent of a SQL Windowing function to which I'd pass a date parameter and that would iterate over the container ids to find the StatusCode for this MAX(Date) and just return all the ContainerIds with this StatusCode up to this date.

But I'm stuck in DAX...

Any help by you DAX Gurus out there would be greatly appreciated...

Thanks forward !

Franck

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Sat, 07 Sep 2019 01:17:20 GMT

Hi franck_axires ,

Just to be sure, you are doing this as a visualization in PBI?I
Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Sat, 07 Sep 2019 01:38:31 GMT

Hi franck_axires ,

A quick data table placed in a Table visualization. Then use filters date is on or before, and Status Code for what is needed.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Sat, 07 Sep 2019 16:23:32 GMT

Hi Nathaniel_C

Thanks for your interest !

The table is only a sample. I'll make nice pie/bar/you-name-it charts for my client.

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Sat, 07 Sep 2019 16:26:45 GMT

Hi Nathaniel_C

Thanks for your reply.

I also need to create a measure that will be used to calculate ratios.

That's why I need the DAX formula...

If you have any clues...

Thanks again !

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Sun, 08 Sep 2019 02:29:49 GMT

Hi franck_axires ,

You need a measure to help calculate ratios, but what you are showing us is a table. What two numbers do you need for the ratio?

We can do tables in the power query editor, or in the Power BI, but what a measure is doing is filtering the rows and returning a value that can go into 1 cell. Like the sum of a column.

So looking for more info here. Thanks!

==============

Get status code = CALCULATE(max(Positions[StatusCode]),Positions[PositionDate]=DATE(2018,5,21)) We can do with parameter.

And there is DATESBETWEEN() which we can probably use.

Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Sun, 08 Sep 2019 03:46:30 GMT

Hi franck_axires ,

So rereading everything, you said, you need to find the container ids for a specific Status Code between the beginning date, and the input date. You will be basing the Status Code on the input date, right? Will the parameter be chosen from a list of dates? Or will any date do with the next or previous date being used. We can do that with concatenex and give you a string of those ids separated with a delimiter, or we can give you a count of those ids.

Let me know!

Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Sun, 08 Sep 2019 04:20:14 GMT

franck_axires

Something like this?

Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Sun, 08 Sep 2019 17:01:07 GMT

Hi franck_axires ,

Lot of work, but very interesting!

First you create a parameter in Power Query based on turning the Date Column into a List. Then incorporate that into a query.

Then if you want to concatenate the IDs:

Concatenate status code =

VAR StatusCode1 = CALCULATE(max(Positions[StatusCode]),Filter(Positions,Positions[PositionDate]=max(GetDate[ParameterDate]))) //what is the StatusCode for this date

return CALCULATE(CONCATENATEX(Positions,Positions[ContainerId], ", "),Filter(Positions,Positions[StatusCode]=StatusCode1 && Positions[PositionDate]<= MAX((GetDate[ParameterDate])))) //use the StatusCode and the Selected Parameter Date to return the ContainerID

NOTE: In StatusCode1 it is =, in the return it is <=

Then the same for the next measure, except we replace CONCATENEX () with COUNTROWS ()

Count of Container ID =

VAR StatusCode1 = CALCULATE(max(Positions[StatusCode]),Filter(Positions,Positions[PositionDate]=max(GetDate[ParameterDate]))) //what is the StatusCode for this date

return CALCULATE(Countrows(Positions),Filter(Positions,Positions[StatusCode]=StatusCode1 && Positions[PositionDate]<= MAX((GetDate[ParameterDate])))) //use the StatusCode and the Selected Parameter Date to return the ContainerID

In the picture below, I also created a couple of measures for checking: 1 to show the date in the parameter, 2 that require manual entry of the StatusCode, and 1 that shows the return of the StatusCode based on parameter.

One Caveat: I saw some comments that parameter are not supported in Power BI service. Check that out, and perhaps you will have to use a date slicer instead of a parameter to publish.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Mon, 09 Sep 2019 08:22:13 GMT

Hi Nathaniel_C

Lots of work indeed ! Thanks a whole lot for digging into this !

I'll be trying it out today and will let you know how it flies.

I will indeed have to publish it on the PBI Service so I'll tell you how it works too.

Thanks again & have a great day,

Franck

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Mon, 09 Sep 2019 09:25:08 GMT

Hi again Nathaniel_C

I will indeed need to use a date slicer in my report as users will need to look into Statuses including in the past.

What would be the syntax then to be able to use a slicer ? Should I just replace

max(GetDate[ParameterDate])

in the DAX query by

MAX(GetDate(Calendar[Date]))

where Calendar is my Date Dimension Table Name ?

I'll keep fiddling in all this in the meantime...😉

Thanks a bunch for your much appreciated help !

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Mon, 09 Sep 2019 14:35:19 GMT

Hi franck_axires ,

How is the fiddling going?

So if you use ALLSELECTED() from the slicer, (I created a new VAR to play with this, and not affect the rest of the code. However the way the Slicer works, it also filters all the dates in your table. So to solve that, I went back to Power Query and used your date column to create a new date table, which allows us to use it as a slicer, but not affect your original table. Works as expected. Try it!

I have to work on something else for awhile, but the one thing I want to research is keeping the new table current as you update your base table. I know there is a way to do it, just have to find it.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Concatenate ContainerID =

VAR SelectedDate = ALLSELECTED(PositionDate[Date]) //PositionDate is the new table with just one column.

VAR StatusCode1 = CALCULATE(max(Positions[StatusCode]),Filter(Positions,(Positions[PositionDate])=SelectedDate))//what is the StatusCode for this date

return CALCULATE(CONCATENATEX(Positions,Positions[ContainerId], ", "),Positions[StatusCode]=StatusCode1,Filter(Positions, Positions[PositionDate]<=SelectedDate))//use the StatusCode and the Selected Parameter Date to return the ContainerID))

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Mon, 09 Sep 2019 14:56:29 GMT

Hi Nathaniel_C

Thanks a lot for your input.

I'm fiddling all the way...😜

Power BI is throwing an error in the CONCATENATEX & COUNTROWS formulas saying it cannot compare a date format to a string.

I have Datetime data on both sides of the = or <= operators and MAX doesn't implicitly convert data to strings so I'm a bit puzzled.

But I'll keep digging and let you know how it all went...!

Thanks again and have fun on your upcoming project !

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Mon, 09 Sep 2019 14:59:54 GMT

By the way, why do you concatenate ContainerIds in your first formula ?

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Mon, 09 Sep 2019 15:55:17 GMT

Hi franck_axires

Go back into PQ and check your new table. You may have to change data type.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Mon, 09 Sep 2019 16:02:49 GMT

franck_axires ,

"By the way, why do you concatenate ContainerIds in your first formula ?"

You said originally,

"I need to find all the ContainerIds with a StatusCode of SUBL up to a certain date. "

Is that not what you were looking to find?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

Nathaniel_C — Mon, 09 Sep 2019 16:36:15 GMT

Hi franck_axires ,

Plug this in and apply to a card.

How many Status Code = CALCULATE(COUNTROWS(Positions),FILTER(Positions,Positions[PositionDate]=ALLSELECTED(PositionDate[Date])))

Select 10/4/2018

Are two codes on one date expected?

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Re: What's the DAX syntax equivalent to a sql windowing

franck_axires — Mon, 25 Nov 2019 13:30:47 GMT

Hi Nathaniel,

Thanks again for the energy you put in solving this problem.

The solution you offered didn't work in the end.

The actual solution resorted to manipulating the filter context with a series of treatas statements.

I'll publish a blog post about it and link to it here.

Thanks again !