topic Re: Rankx with ALLSELECT +ALLEXCEPT in DAX Commands and Tips

Rankx with ALLSELECT +ALLEXCEPT

Anonymous — Fri, 29 Sep 2023 13:21:11 GMT

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

Re: Rankx with ALLSELECT +ALLEXCEPT

123abc — Sat, 30 Sep 2023 06:51:37 GMT

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.

Re: Rankx with ALLSELECT +ALLEXCEPT

Anonymous — Sat, 30 Sep 2023 15:38:45 GMT

Thanks for the lengthy expalantion.
But the issue is in DAX 01 result respect the external filter context :e:g calender month.

Re: Rankx with ALLSELECT +ALLEXCEPT

123abc — Sat, 30 Sep 2023 18:14:42 GMT

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.

Re: Rankx with ALLSELECT +ALLEXCEPT

Dangar332 — Sat, 30 Sep 2023 18:22:53 GMT

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.

Re: Rankx with ALLSELECT +ALLEXCEPT

Dangar332 — Sat, 30 Sep 2023 18:34:06 GMT

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.