Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi Everyone,
I am trying to differentiated the below two dax snippets based on the outer filter context.
DAX 01
RANKX (
ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ),
CALCULATE (
[Total AR Amount],
ALLEXCEPT ( CUSTOMERS, CUSTOMERS[CUSTOMER_PARENT_ID] )
),
,
DESC,
DENSE
)
With this DAX if i have an external slicer let’s say calender month,if i select last month i got the last month total AR amount ,but i have used allexcept at the beginning ,still i got the last month value.how that happen?
DAX 02
RANKX (
ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ),
[Total AR Amount],
,
DESC,
DENSE
)
What would be the difference in this with respect to above DAX 01 and if have an external slicer like calender month.
Thanks
hi, @nalakanayana
RANKX (
ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ),
CALCULATE (
[Total AR Amount],
ALLEXCEPT ( CUSTOMERS, CUSTOMERS[CUSTOMER_PARENT_ID] )
),
,
DESC,
DENSE
)
here you use allexcept as calculatemodifier so it remove only filter context not ignoring outer filter value
for ignoring outer filter value you will have to use it in table function not in calculate modifier.
In Power BI, the behavior of DAX measures can sometimes be a bit complex due to the interaction between filter contexts. Let's break down the two DAX snippets you provided and explain the differences in their behavior:
DAX 01:
RANKX ( ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ), CALCULATE ( [Total AR Amount], ALLEXCEPT ( CUSTOMERS, CUSTOMERS[CUSTOMER_PARENT_ID] ) ), , DESC, DENSE )
DAX 02:
RANKX (
ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ),
[Total AR Amount],
,
DESC,
DENSE
)
In both cases, you are using the RANKX function to rank customers based on their [Total AR Amount], considering the filter context applied to CUSTOMERS[CUSTOMER_PARENT_ID].
Now, let's discuss the differences:
DAX 01:
DAX 02:
So, the key difference is in how the filter context is applied to the CUSTOMERS[CUSTOMER_PARENT_ID] column:
The choice between these two DAX expressions depends on your specific requirements and how you want the ranking to respond to slicers and filter contexts. Use DAX 01 when you want to rank based on the parent customers without considering other filters, and use DAX 02 when you want to rank based on the current filter context, including slicers like Calendar Month.
Thanks for the lengthy expalantion.
But the issue is in DAX 01 result respect the external filter context :e:g calender month.
hi, @nalakanayana
use
RANKX (
ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ),
CALCULATE (
[Total AR Amount],
all(CUSTOMERS),values(CUSTOMERS[CUSTOMER_PARENT_ID]) ----- (here all remove all filter context from expanded table but values restore filter context of CUSTOMERS[CUSTOMER_PARENT_ID] )
),
,
DESC,
DENSE
)
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Pleae try this:
RANKX (
ALLSELECTED ( CUSTOMERS[CUSTOMER_PARENT_ID] ),
CALCULATE (
[Total AR Amount],
ALLEXCEPT ( CUSTOMERS, CUSTOMERS[CUSTOMER_PARENT_ID] )
),
,
DESC,
DENSE
)
The behavior of DAX 01 with respect to the external slicer (calendar month) is as follows:
ALLSELECTED(CUSTOMERS[CUSTOMER_PARENT_ID]) removes filters on the CUSTOMERS[CUSTOMER_PARENT_ID] column but retains filters from other slicers, including the calendar month slicer.
CALCULATE([Total AR Amount], ALLEXCEPT(CUSTOMERS, CUSTOMERS[CUSTOMER_PARENT_ID])) calculates the [Total AR Amount] within the filter context specified by ALLEXCEPT. However, it only preserves the filter context for CUSTOMERS[CUSTOMER_PARENT_ID], not for other columns like calendar month.
So, if you select a calendar month using an external slicer, DAX 01 will still consider the filter on CUSTOMERS[CUSTOMER_PARENT_ID] but not the filter on the calendar month. This means that the rank will be based on the filtered [Total AR Amount] within the selected CUSTOMERS[CUSTOMER_PARENT_ID] group, regardless of the calendar month slicer's selection.
If you are observing different behavior, it may be due to other factors or interactions within your Power BI or Analysis Services model. DAX evaluation can be influenced by various factors such as relationships between tables, slicer interactions, and measures in your model. Please make sure that there are no additional filters or measures affecting the result or share more details about your model for a more specific analysis.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
User | Count |
---|---|
20 | |
14 | |
11 | |
8 | |
6 |
User | Count |
---|---|
23 | |
23 | |
20 | |
15 | |
10 |