Forum Discussion
Rankx with ALLSELECT +ALLEXCEPT
Thanks for the lengthy expalantion.
But the issue is in DAX 01 result respect the external filter context :e:g calender month.
Community ChampionPleae 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.