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

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

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

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

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

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

    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 !