Forum Discussion

franck_axires's avatar
franck_axires
Frequent Visitor
7 years ago

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

    • franck_axires's avatar
      franck_axires
      Frequent Visitor
      7 years ago

      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.

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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

     

    • franck_axires's avatar
      franck_axires
      Frequent Visitor
      7 years ago

      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's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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