Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
PositionId | PositionDate | ContainerId | StatusCode |
34408 | 08/03/2018 | 1 | RCVE |
95676 | 10/04/2018 | 1 | RCVF |
100525 | 12/04/2018 | 1 | SUBL |
101685 | 21/05/2018 | 1 | RTSL |
111024 | 23/05/2018 | 1 | RCVF |
585046 | 15/01/2019 | 2 | RCVE |
595659 | 22/01/2019 | 2 | SUBL |
622710 | 06/02/2019 | 2 | RTSL |
628154 | 08/02/2019 | 2 | DVSF |
255695 | 05/07/2018 | 3 | TRLE |
294286 | 26/07/2018 | 3 | SUBL |
341214 | 22/08/2018 | 3 | RTSL |
355613 | 30/08/2018 | 3 | OTSH |
89795 | 06/04/2018 | 4 | DVSE |
95552 | 10/04/2018 | 4 | OTSH |
97635 | 11/04/2018 | 4 | SUBL |
79650 | 26/03/2018 | 5 | RCVE |
79657 | 27/03/2018 | 5 | SUBL |
202818 | 05/06/2018 | 5 | RTSL |
202819 | 06/06/2018 | 5 | TRLE |
64422 | 22/03/2018 | 6 | RCVF |
193698 | 27/05/2018 | 6 | SUBL |
196336 | 03/06/2018 | 6 | LVSF |
206919 | 07/06/2018 | 6 | DVSF |
I need to find all the ContainerIds with a StatusCode of SUBL up to a certain date. For instance :
- on 01/04/2018, the results should be :
PositionId | PositionDate | ContainerId | StatusCode |
79657 | 27/03/2018 | 5 | SUBL |
- on 01/05/2018, I should find :
PositionId | PositionDate | ContainerId | StatusCode |
79657 | 27/03/2018 | 5 | SUBL |
97635 | 11/04/2018 | 4 | SUBL |
100525 | 12/04/2018 | 1 | SUBL |
- and on 01/02/2019 I should find
PositionId | PositionDate | ContainerId | StatusCode |
79657 | 27/03/2018 | 5 | SUBL |
97635 | 11/04/2018 | 4 | SUBL |
100525 | 12/04/2018 | 1 | SUBL |
193698 | 27/05/2018 | 6 | SUBL |
294286 | 26/07/2018 | 3 | SUBL |
595659 | 22/01/2019 | 2 | SUBL |
It seems to me to be the equivalent of a SQL Windowing function to which I'd pass a date parameter and that would iterate over the container ids to find the StatusCode for this MAX(Date) and just return all the ContainerIds with this StatusCode up to this date.
But I'm stuck in DAX...
Any help by you DAX Gurus out there would be greatly appreciated...
Hi @franck_axires ,
A quick data table placed in a Table visualization. Then use filters date is on or before, and Status Code for what is needed.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
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 !
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!
Proud to be a Super User!
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
Proud to be a Super User!
Something like this?
Nathaniel
Proud to be a Super User!
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:
Proud to be a Super User!
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
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 !
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
Proud to be a Super User!
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 !
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
Proud to be a Super User!
By the way, why do you concatenate ContainerIds in your first formula ?
"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
Proud to be a Super User!
Hi @franck_axires ,
Plug this in and apply to a card.
Proud to be a Super User!
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 !
Hi @franck_axires ,
Just to be sure, you are doing this as a visualization in PBI?I
Nathaniel
Proud to be a Super User!
Hi @Nathaniel_C
Thanks for your interest !
The table is only a sample. I'll make nice pie/bar/you-name-it charts for my client.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |