How to Combine Contradictory Filters in DAX
TL;DR · AI 摘要
在DAX中处理矛盾筛选器时,通过改进数据模型和使用Power BI Dataflow可有效跟踪订单状态变化。
核心要点
- 使用Power BI Dataflow可减少源系统数据提取负载
- 订单状态跟踪需在数据模型中增加状态转换日期字段
- DAX的#duration()函数可计算状态持续时间
结构提纲
按章节快速跳转。
思维导图
用一张图看清主题之间的关系。
查看大纲文本(无障碍 / 无 JS 友好)
- DAX矛盾筛选器处理
- 数据模型改进
- 状态转换日期字段
- Power BI Dataflow应用
- 实施方法
- 增量数据提取
- 源系统协作
金句 / Highlights
值得收藏与分享的关键句。
使用Power BI Dataflow可减少源系统数据提取负载,避免每日全量提取
订单状态跟踪需要在数据模型中增加状态转换日期字段
DAX的#duration()函数可计算状态持续时间,但需要完整状态历史数据
How to Combine Contradictory Filters in DAX | Towards Data Science
Data Science
How to Combine Contradictory Filters in DAX
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.
Salvatore Cagliari
November 4, 2026
7 min read
Prefer us on Google
Share
Image by 愚木混株 Yumu via Unsplash
Introduction
My last article covered how to handle nested measures that modify the same filter as the calling measure.
If you didn't read it, here it is:
How to Solve Issues When You Nest Measures While Overwriting the Same Filter
In DAX, we reuse existing measures all the time when writing new measures. What happens when we write a measure based on another measure and try to change a filter already set in the nested measure?
Salvatore Cagliari · 7 min read
But shortly after, I encountered a similar situation where that approach wouldn’t work.
What happened there?
The situation and the data
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
- Closed
The question now is: how long did every closed order stay in each status?
So, when counting the orders, I add a filter for Closed orders.
But how can I track changes between states?
What is the challenge?
When you have an orders table, this table might contain a status column, which marks the current state of the order.
But if I only consider closed orders, how can I track changes for the other states?
If I look only at closed orders, I will not see orders in any other state.
This contradiction requires a solution that modifies the data model.
Easy solution
Some ERP systems might have an orders table like this:
Figure 1 - A simple orders table containing the dates of the status switches (Image by the Author)
With this, it's easy to calculate the days between states, for example, using the #duration() function in Power Query.
You can create multiple measures summing the columns containing the durations between the states.
But what happens when this is not the case?
When the orders switch between states without any tracking.
Or when you want to create only one measure and slice between the states? If the solution allows this, you can calculate the total time from order entry to order closure.
Track changes in the Source
Now, it depends on what is possible and the data platform the source system runs on.
If it is a closed system, for example, you cannot access the underlying data; you must perform multiple data extractions per day to track same-day changes.
This can add load to the source system. Therefore, contact the application provider for help or advice on meeting the requirements.
One possible solution to reduce the load on the source system is to extract only the required table by using a Power BI Dataflow. After this, load the data from the Dataflow into Power BI.
In case you have access to the database system of the application, you might be able to add a CDC (Change Data Capture) solution.
SQL Server, for example, includes CDC functionality.
CDC enables you to record all changes to all the data in a table.
This way, you can track all changes, including the exact date and time of the changes.
You can read more about CDC in SQL Server here .
This approach allows you to get a historised version of the orders table that includes the date and time of each order state change.
Describing CDC in SQL Server is out of scope for this article.
Please read the documentation above for more information.
There is also plenty of information online describing this feature.
Add historization
Since I can't access such an application, I must simulate the data. For this, I take the table shown above and manipulate it to get the desired result.
The target is to derive a historised version of the orders table directly.
Here is how I calculated a historised table from the table above.
To improve the results, I added a time component to each date. Then I loaded the data into a SQL Server database.
This is the data in the table:
Figure 2 - Order table loaded into SQL Server with a time component added to each date (Image by the Author)
Next, I calculate the time difference in hours for each state and the total duration between the order and the closure date/time:
sql
SELECT [OrderNo]
,[Customer No]
,[Order Date]
,DATEDIFF(mi, [Order Date], [Start preparation]) / 60.0 AS [TimeInOrderInHrs]
,[Start preparation]
,DATEDIFF(mi, [Start preparation], [Shipping Date]) / 60.0 AS [TimeInPrepInHrs]
,[Shipping Date]
,DATEDIFF(mi, [Shipping Date], [Billing Date]) / 60.0 AS [TimeInShippingInHrs]
,DATEDIFF(mi, [Order Date], [Shipping Date]) / 60.0 AS [TimeOrderToShippinhgInHrs]
,[Billing Date]
,DATEDIFF(mi, [Billing Date], [Closing Date]) / 60.0 AS [TimeInBillingInHrs]
,[Closing Date]
,DATEDIFF(mi, [Order Date], [Closing Date]) / 60.0 AS [TimeForFullOrderInHrs]
FROM [dbo].[OrdersWithStates];As you can see, I use the DATEDIFF() function to calculate the difference in minutes, then I divide the result by 60 to get fractions of an hour.
The result of this query is the following:
Figure 3 - Results of the order table with the durations in hours (Image by the Author)
Based on these results, I can unpivot this data to get a table with one row per order and state.
This is the SQL code for this:
WITH [Prep]
AS
(
SELECT [OrderNo]
,[Customer No]
,[Order Date]
,DATEDIFF(mi, [Order Date], [Start preparation]) / 60.0 AS [TimeInOrderInHrs]
,[Start preparation]
,DATEDIFF(mi, [Start preparation], [Shipping Date]) / 60.0 AS [TimeInPrepInHrs]
,[Shipping Date]
,DATEDIFF(mi, [Shipping Date], [Billing Date]) / 60.0 AS [TimeInShippingInHrs]
,DATEDIFF(mi, [Order Date], [Shipping Date]) / 60.0 AS [TimeOrderToShippinhgInHrs]
,[Billing Date]
,DATEDIFF(mi, [Billing Date], [Closing Date]) / 60.0 AS [TimeInBillingInHrs]
,[Closing Date]
,DATEDIFF(mi, [Order Date], [Closing Date]) / 60.0 AS [TimeForFullOrderInHrs]
FROM [dbo].[OrdersWithStates]
)
SELECT [OrderNo]
,[Customer No]
,[Order Date]
,[Start preparation]
,[Shipping Date]
,[Billing Date]
,[Closing Date]
,[DatesAndTimes]
,[Duration]
FROM (SELECT [OrderNo]
,[Customer No]
,[Order Date]
,[TimeInOrderInHrs]
,[Start preparation]
,[TimeInPrepInHrs]
,[Shipping Date]
,[TimeInShippingInHrs]
,[TimeOrderToShippinhgInHrs]
,[Billing Date]
,[TimeInBillingInHrs]
,[Closing Date]
,[TimeForFullOrderInHrs]
FROM [Prep]) AS [p]
UNPIVOT
(
[Duration] FOR [DatesAndTimes]
IN
([TimeInOrderInHrs]
,[TimeInPrepInHrs]
,[TimeInShippingInHrs]
,[TimeOrderToShippinhgInHrs]
,[TimeInBillingInHrs]
,[TimeForFullOrderInHrs])
)
AS [unpvt];Figure 4 - Unpivoted order table with the durations per status. This table includes the duration between the order date and the total time between the order date and the shipping date (Image by the Author)
This table can now be loaded into Power BI to start calculations.
Before doing that, I will add some orders without a closing date.
Additionally, I added date columns based on the columns with datetime values and additional columns to mark the orders by state:
Figure 5 - The full orders table including the date columns and the columns to mark if the orders are in specific states (Image by the Author)
The columns marked in blue are the date columns. I need them to be able to add relationships to the date table. This won't work with the original columns containing time because the date column in the date table doesn't include time; therefore, the relationship won't find matching rows.
The red-marked columns are flag columns that mark orders in specific states.
Add DAX code
The only needed measure is to aggregate the Duration column.
But I must exclude the Total Duration:
Duration =
CALCULATE(
SUM('OrdersStateDuration'[Duration])
,'OrdersStateDuration'[DatesAndTimes] <> "TimeOrderToShippinhgInHrs"
)Next, I add this to a Matrix visual using the Calendar Hierarchy for the Row:
Figure 6 - The Total Duration times, with the exclusion of the Duration for the "TotalOrderToShippingInHrs" excluded (Image by the Author)
Next, I add the [DateAndTimes] column from the OrdersStateDuration table to the Matrix columns.
Lastly, I add a filter to include only the closed orders:
Figure 7 - Final results with the Duration by states and the filter to include only closed orders (Image by the Author)
Now I have the needed result, showing the time needed for each state.
Another useful metric would be the average time for each state.
But this is just a copy of the existing measure, which calculates the sum of the duration.
Solving this challenge depends on two factors:
- How the data can be extracted from the source system
- How to track the evolution of an order with sufficient precision
These are classic data engineering challenges that vary by data source. There is no standard recipe, as it depends on the source system and data format.
I’ve found systems that deliver the orders table with the change history, but most systems don’t.
In a related project, I extracted data from Jira, and that system has a history table that records all changes to all fields, with precision up to the second. There, it was easy to transform the data to the required format.
Jira has even a table where each issue is recorded with the time it spend in each status. That table is extremely helpful when a measure must be created for such a scenario.
Once the data is in a format similar to the data shown above, Power BI doesn’t require complex logic so that it can deliver results very efficiently.
In my experience, intelligent data modelling always beats complex DAX logic.
I hope you’ve found this approach useful, and I'm happy to have shown you something new.
References
Description on how to work with durations in Power Query:
https://learn.microsoft.com/en-us/powerquery-m/durations
Microsoft documentation for Change Data Capture in SQL Server:
https://learn.microsoft.com/en-gb/sql/relational-databases/track-changes/about-change-data-capture-sql-server
I created the data used here manually. There is no reference to real data.