cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
franck_axires Frequent Visitor
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
16 REPLIES 16
Nathaniel_C Super Contributor
Super Contributor

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

Hi @franck_axires ,

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

Nathaniel_C Super Contributor
Super Contributor

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

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

 

franck_axires Frequent Visitor
Frequent Visitor

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

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.

franck_axires Frequent Visitor
Frequent Visitor

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

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 !

Nathaniel_C Super Contributor
Super Contributor

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

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
Nathaniel_C Super Contributor
Super Contributor

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

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

Nathaniel_C Super Contributor
Super Contributor

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

@franck_axires 

Something like this?

Nathaniel

shipping 2.PNG

Nathaniel_C Super Contributor
Super Contributor

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

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
Highlighted
franck_axires Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 134 members 1,773 guests
Please welcome our newest community members: