Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
franck_axires
Frequent Visitor

What's the DAX syntax equivalent to a sql windowing

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 :

PositionIdPositionDateContainerIdStatusCode
3440808/03/20181RCVE
9567610/04/20181RCVF
10052512/04/20181SUBL
10168521/05/20181RTSL
11102423/05/20181RCVF
58504615/01/20192RCVE
59565922/01/20192SUBL
62271006/02/20192RTSL
62815408/02/20192DVSF
25569505/07/20183TRLE
29428626/07/20183SUBL
34121422/08/20183RTSL
35561330/08/20183OTSH
8979506/04/20184DVSE
9555210/04/20184OTSH
9763511/04/20184SUBL
7965026/03/20185RCVE
7965727/03/20185SUBL
20281805/06/20185RTSL
20281906/06/20185TRLE
6442222/03/20186RCVF
19369827/05/20186SUBL
19633603/06/20186LVSF
20691907/06/20186DVSF

 

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 :

PositionIdPositionDateContainerIdStatusCode
7965727/03/20185SUBL

 

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

PositionIdPositionDateContainerIdStatusCode
7965727/03/20185SUBL
9763511/04/20184SUBL
10052512/04/20181SUBL

 

- and on 01/02/2019 I should find

PositionIdPositionDateContainerIdStatusCode
7965727/03/20185SUBL
9763511/04/20184SUBL
10052512/04/20181SUBL
19369827/05/20186SUBL
29428626/07/20183SUBL
59565922/01/20192SUBL

 

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
17 REPLIES 17
Nathaniel_C
Super User
Super User

Hi @franck_axires ,

Shipping1.PNG

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

 

 

Shipping.PNG

 

 

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 !

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@franck_axires 

Something like this?

Nathaniel

shipping 2.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.
 
shipping 3.PNG
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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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 !

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 !

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 !

Nathaniel_C
Super User
Super User

Hi @franck_axires ,

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors