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?
It’s because of the interaction between ALLSELECTED and ALLEXCEPT in the CALCULATE context.
ALLSELECTED(CUSTOMERS[CUSTOMER_PARENT_ID]) keeps the filter context for CUSTOMERS[CUSTOMER_PARENT_ID] as it is, while also respecting any external slicers like “calendar month.” So, if you’ve selected “last month” in an external slicer, this filter context passes through to the CALCULATE function.
Inside the CALCULATE function, the ALLEXCEPT function removes all filters on the CUSTOMERS table except for CUSTOMER_PARENT_ID. However, since the external slicer for “calendar month” is still in the outer filter context (thanks to ALLSELECTED), it remains unaffected.
When CALCULATE evaluates [Total AR Amount], it does so with two filters:
The “last month” filter from the external slicer
The CUSTOMER_PARENT_ID filter
You get the total AR amount for the last month because the external slicer’s filter for “calendar month” is respected by both ALLSELECTED and CALCULATE.
DAX 02 does not alter the filter context for the [Total AR Amount] measure, meaning it will respect all existing filters, including those from external slicers.
Thanks much.
In [DAX 01] is there an impact to the CUSTOMERS[CUSTOMER_PARENT_ID] column values by the external slicer(eg: calender month)?
Does the external slicers affect only to calculate statement(values) in this scenario?
(There is no direct relationship with customer and date table)
If the answer is yes in which occasions “allselected” syntax would be useful for this DAX’es ?
sometimes you just have try it and see what happen. If I remember correctly, there is a video on youtube using Dax Studio that you can take the Dax formula apart. I also think there is something on Enterprise DNA too.