Sometimes we need to calculate results based on contradictory filters. Without any action, these filters will lead to an empty result. Let's see how we can improve the data model to make it work.
Imagine a business that receives orders and wants to track the time an order stays in each status. An order can be in one of the following statuses: Open, Pending, Preparing for shipment, Shipped, Billing, or Closed. The question is: how long did every closed order stay in each status? When counting orders, we add a filter for Closed orders. But how can we track changes between states? If we only consider closed orders, we will not see orders in any other state. This contradiction requires a solution that modifies the data model.
Some ERP systems have an orders table containing the dates of the status switches. With this, it's easy to calculate the days between states using the #duration() function in Power Query. However, when orders switch between states without any tracking, or when you want to create only one measure and slice between the states, a different approach is needed. One possible solution is to extract only the required table by using a Power BI Dataflow. If you have access to the database system, you might be able to add a CDC (Change Data Capture) solution, such as SQL Server's CDC functionality, which records all changes to all the data in a table. This approach allows you to get a historised version of the orders table that includes the date and time of each order state change.
Source: Towards Data Science · Summarized by HeadlinesBriefing