Forum Discussion
DAX Function which Ignores User Filter
How do I ensure a total figure is not affected by any filters selected by the user. I know I could use “Edit interactions” and switch of the filter interaction for the visual; however I would like to achieve this via DAX.
Background context: I would like to ‘Distinct Count’ all items on a column. Each row represents a classroom workshop. Workshops are planned in advance on a yearly base. Occasionally a workshop needs to be cancelled due to below minimum confirmed attendees. To report all workshops scheduled Vs. those cancelled and those live I want an figure unaffected by any filters which I can use as a Denominator.
My inputs would be:
Table name: [Workshops Plan] Total workshops scheduled or as a short hand to make it easier to read [TOTWrkshop. Sch]
[TOTWrkshop. Sch] = District Count of workshop IDs within column [IDs].
I would also like a total figure for all cancelled workshops, which would be: Total Cancelled Workshops i.e. [TOTWrkshop. Cnl]
[TOTWrkshop. Cnl] = District Count of workshop IDs within column [IDs] AND with a status of “Cancelled” within column [Workshop Status].
To acheive this with dax you will want to use the ALL() function within a CALULATE().
With your measure [TOTWrkshop. Sch] measure you will want to modify it from
DISTINCTCOUNT ( Workshops[ID] )
to
CALCULATE ( DISTINCTCOUNT ( Workshops[ID] ), ALL ( Workshops ) )
This will mean that any filter coming from within the workshops table will be removed.
This is assuming that you only have one table, if you have multiple tables then you may have to add more ALL statements depending on the model structure.
I hope this helps
2 Replies
To acheive this with dax you will want to use the ALL() function within a CALULATE().
With your measure [TOTWrkshop. Sch] measure you will want to modify it from
DISTINCTCOUNT ( Workshops[ID] )
to
CALCULATE ( DISTINCTCOUNT ( Workshops[ID] ), ALL ( Workshops ) )
This will mean that any filter coming from within the workshops table will be removed.
This is assuming that you only have one table, if you have multiple tables then you may have to add more ALL statements depending on the model structure.
I hope this helps
Hi Anonymous, “You little blinder”, fab – this works great. I tried to read up on the ALL function; however it just didn’t click until you explained it. Thank you.
