cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Reply
franck_axires Frequent Visitor
Frequent Visitor

Re: What's the DAX syntax equivalent to a sql windowing

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 !

Nathaniel_C Super Contributor
Super Contributor

Re: What's the DAX syntax equivalent to a sql windowing

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))

 

 

Highlighted
franck_axires Frequent Visitor
Frequent Visitor

Re: What's the DAX syntax equivalent to a sql windowing

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 !

franck_axires Frequent Visitor
Frequent Visitor

Re: What's the DAX syntax equivalent to a sql windowing

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

Nathaniel_C Super Contributor
Super Contributor

Re: What's the DAX syntax equivalent to a sql windowing

Hi @franck_axires 

Go back into PQ and check your new table. You may have to change data type.

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 Super Contributor
Super Contributor

Re: What's the DAX syntax equivalent to a sql windowing

@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 Super Contributor
Super Contributor

Re: What's the DAX syntax equivalent to a sql windowing

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
 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 โ€“ Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 284 members 2,964 guests
Please welcome our newest community members: