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 : ...
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
  • 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