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

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

    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
    • franck_axires's avatar
      franck_axires
      Frequent Visitor
      7 years ago

      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

      • franck_axires's avatar
        franck_axires
        Frequent Visitor
        7 years ago

        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 !