Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
nalakanayana
Frequent Visitor

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 5
Dangar332
Super User
Super User

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 valu

for ignoring outer filter value you will have to use it in table function not in calculate modifier.

123abc
Community Champion
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.

 

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:

  1. 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.

  2. 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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.