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 would like to create a measure that outputs a sum of two other measures, however the two measures have different filter rules( one measure needs to take into account the filter , while other needs to ignore it
Here is an example :
Measure 1 WIP Orders = CALCULATE(Counta(Orders[ID]), FILTER(ALLEXCEPT(Orders [ Order Type], Orders[order raised date] <= MAX Order Raised Dates [ Date] && Orders[completed date]> MAX( Order Raised Dates [ Date]) || ISBLANK( Orders[Completed Date]))) - No order status filter applied
Measure 2 Cancelled Orders = CALCULATE(COUNTA(Orders[ID]), ALLEXCEPT(Orders[Status]) - No date filter applied
Measure 3 Combined total = WIP Orders + Cancelled Orders ( with Order Status filter applied at visual level)
If I use measures 1 & 2 individually they are showing correct output, however in the measure 3 Order status filter seems to be applied to both measures is there a way to only apply order status filter to 1 measure when suming two ?
I'm using live connection therefore cannot add any calculated columns.
Thanks
Solved! Go to Solution.
Hi @Anonymous
Sorry I hadn't tested the measure before posting it. Please try the new one bellow. I created a relationship between 'Orders'[order raised date] and 'Order Raised Dates'[Date]. Also, the testing column [Group] can filter Measure 1. You can download the attachment to see details.
Measure 1 =
VAR __maxOrderRaisedDate = MAX ( 'Order Raised Dates'[Date] )
RETURN
CALCULATE (
COUNTA ( Orders[ID] ),
ALL ( Orders[Status] ),
ALL ( 'Order Raised Dates'[Date] ),
Orders[order raised date] <= __maxOrderRaisedDate,
Orders[completed date] > __maxOrderRaisedDate || ISBLANK ( Orders[Completed Date] )
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Anonymous
Try avoid using ALLEXCEPT within FILTER function. Instead, you could try a mode like
WIP Orders =
VAR __maxOrderRaisedDate =
MAX ( 'Order Raised Dates'[ Date] )
RETURN
CALCULATE (
COUNTA ( Orders[ID] ),
ALLEXCEPT ( Orders, Orders[ Order Type], Orders[xxxx] ),
( Orders[order raised date] <= __maxOrderRaisedDate
&& Orders[completed date] > __maxOrderRaisedDate )
|| ISBLANK ( Orders[Completed Date] )
)
I feel it difficult explaining the reason. You may refer to this thread (Filter and Allexcept) for a better explanation.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thanks @v-jingzhang I tried to tweak the formula but i am now getting error message : "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression." I read that including related statement might solve the problem however not sure where it would need to go given that I am using a variable ?
Hi @Anonymous
Sorry I hadn't tested the measure before posting it. Please try the new one bellow. I created a relationship between 'Orders'[order raised date] and 'Order Raised Dates'[Date]. Also, the testing column [Group] can filter Measure 1. You can download the attachment to see details.
Measure 1 =
VAR __maxOrderRaisedDate = MAX ( 'Order Raised Dates'[Date] )
RETURN
CALCULATE (
COUNTA ( Orders[ID] ),
ALL ( Orders[Status] ),
ALL ( 'Order Raised Dates'[Date] ),
Orders[order raised date] <= __maxOrderRaisedDate,
Orders[completed date] > __maxOrderRaisedDate || ISBLANK ( Orders[Completed Date] )
)
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
@Anonymous , Some logic correction is required in 1st formula
example
CALCULATE(Counta(Orders[ID]), FILTER(allselected(Orders), [Order Type] = max([Order Type]) && Orders[order raised date] <= max(Orders[order raised date]) && ( Orders[completed date]> MAX( Order Raised Dates [ Date]) || ISBLANK( Orders[Completed Date])) ) )
Thanks @amitchandak would you be able to explain the change ? As I have a few other columns that are included in Allexcept section of the measure which I kept it out for simplicity. When I tried to replace ALLEXCEPT with ALLSELECTED I am getting errors also why does MAX order type need to be specified ?
Thanks
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 |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |