Thanks for taking the time to read my post, I am fairly new to Power BI, therefore, my DAX skills are limited at the mo, this is the first time I’m posting in this forum too.
What I am looking to achieve is the ability to find out how many orders we had at any given time by order status. The issue I’m currently faced with is that within our data warehouse we have an order status history table which timestamps every order as it goes through the different status. Meaning one order can have one than one row. Therefore i need to create a measure which only counts the latest order status.
Ok yes I demo model would help here for a quicker solution, but…
I can already see many things you need to fix straight away. This calculation is actually not difficult at all if you have the right setup in your model.
Here’s the most important course to go through early on for you I believe.
First you should look to merge you dimOrders table with you factOrders table. There’s no need for both of these.
Then you can simply have a one to many relationship from your date table to your Orders table.
Also the Date table should be connected to your Order history table with an active relationship. An inactive one serves no purpose here. You could also merge those two tables (Order Statuses and Order Status History) to simplify things even further.
Always look to simplify in the the data model. This is what I always do myself.
Next one, don’t have a date and time column. You need to change this to just a date, so that is can link up to your date table (date column).
Then once you made all these change you calculation should be very simple. Something like the below.
What i want to do is create a measure that will only display and count the latest order status name.
For testing i have a selected an Order ID and can see that this order goes through 6 status changes, 5 changes throughout one week (WK26) and the final status change in the next (WK27).
I want to be able to select week 26 and only count and display that orders latest status for this example it would be the “To Be Collect” status. If i was to select WK 27 then this would change to “Fully Despatched”
The problem i am currently facing is when i select wk 26 it is displaying all the statuses the order went through that week, in the total it is only counting one but i am seeing more than one status. i am only interested in seeing the latest in that week.
This model doesn’t look right to me and is likely why there’s still a bit of confusion.
First it’s unlike the inactive relationship from dates to the order history is what’s required.
To me both your Orders and Order Status History are two seperate fact tables. You should have then at the same level and both have active relationship with the date table. Both one to many as well, with the one side being the date table.
Once this is setup the formula shouldn’t be too difficult.
Once you have this then all you need to do is calculate the last order status name within the specified context and from this use a relatively simple CALCULATE/FILTER logic.
Getting the model setup correctly here is the main piece of the puzzle.
Once that is setup correctly give the above logic another go.
If still stuck a demo model of the scenario would be the best as things could be looked at quickly from there and better solutions provided.
