Forum Discussion
Rankx with ALLSELECT +ALLEXCEPT
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
5 Replies
- Dangar332
Resident Rockstar
hi, Anonymous
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.
- 123abc
Community Champion
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:
- Uses the ALLEXCEPT function to remove filters from all columns in the CUSTOMERS table except CUSTOMERS[CUSTOMER_PARENT_ID].
- This means that if you have an external slicer, like Calendar Month, selecting a particular month will not affect the filter on CUSTOMERS[CUSTOMER_PARENT_ID], so the ranking is based only on the parent customers regardless of the selected month.
DAX 02:
- Does not use the ALLEXCEPT function, which means it considers all filters applied to the entire table CUSTOMERS, including any slicers.
- If you have an external slicer, like Calendar Month, selecting a specific month will filter both CUSTOMERS[CUSTOMER_PARENT_ID] and [Total AR Amount], affecting the ranking calculation. In other words, the ranking will consider the selected month's filter context.
So, the key difference is in how the filter context is applied to the CUSTOMERS[CUSTOMER_PARENT_ID] column:
- DAX 01 explicitly removes filters on all columns except CUSTOMERS[CUSTOMER_PARENT_ID] before calculating [Total AR Amount]. Thus, it ignores the Calendar Month slicer.
- DAX 02 considers all filters, including the Calendar Month slicer, when calculating [Total AR Amount].
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.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.- Dangar332
hi, Anonymous
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.
