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 ,

How is the fiddling going?

 

So if you use ALLSELECTED() from the slicer, (I created a new VAR to play with this, and not affect the rest of the code. However the way the Slicer works, it also filters all the dates in your table. So to solve that, I went back to Power Query and used your date column to create a new date table, which allows us to use it as a slicer, but not affect your original table. Works as expected. Try it!

 

I have to work on something else for awhile, but the one thing I want to research is keeping the new table current as you update your base table. I know there is a way to do it, just have to find it.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Concatenate ContainerID =
VAR SelectedDate = ALLSELECTED(PositionDate[Date]) //PositionDate is the new table with just one column.
VAR StatusCode1 = CALCULATE(max(Positions[StatusCode]),Filter(Positions,(Positions[PositionDate])=SelectedDate))//what is the StatusCode for this date
return CALCULATE(CONCATENATEX(Positions,Positions[ContainerId], ", "),Positions[StatusCode]=StatusCode1,Filter(Positions, Positions[PositionDate]<=SelectedDate))//use the StatusCode and the Selected Parameter Date to return the ContainerID))

 

 

franck_axires's avatar
franck_axires
Frequent Visitor
7 years ago

Hi Nathaniel_C 

Thanks a lot for your input.

I'm fiddling all the way...😜

Power BI is throwing an error in the CONCATENATEX & COUNTROWS formulas saying it cannot compare a date format to a string.

I have Datetime data on both sides of the = or <= operators and MAX doesn't implicitly convert data to strings so I'm a bit puzzled.

But I'll keep digging and let you know how it all went...!

Thanks again and have fun on your upcoming project !