Forum Discussion
ALL with ALLEXCEPT
- 7 years ago
how about just this:
Measure 2 =
CALCULATE(
[Measure 1],
ALLEXCEPT(
'Calendar Date Detail',
'Calendar Date Detail'[relative_date_no]
)
)you may need to add other columns from the calendar table if the users are allowed to filter on them, or if they are displayed in the visual
- Anonymous7 years ago
The family of ALL* functions, when used as top level functions in CALCULATE, remove filters (they do not return tables) but if you put a whole table inside them, they'll remove filters from the EXPANDED VERSIONS of the tables, not just the tables as you thought (I know you did from your code). Hence, putting ALL( Stock ) will remove all filters from all related tables (on condition that the other tables are connected to Stock in a 1:many fashion, which I think is the case here). To know how the ALL* functions work you need to know the theory of expanded tables, by the way.
So, in order to get what you want you have to write:
calculate ( [Measure 1], -- please give a meaningful name, Measure 1 means NOTHING all ( Stock ), -- remove all filters 'Calendar Date Detail' -- restore the filters on Calendar Date Datail only )
Best
Darek
The family of ALL* functions, when used as top level functions in CALCULATE, remove filters (they do not return tables) but if you put a whole table inside them, they'll remove filters from the EXPANDED VERSIONS of the tables, not just the tables as you thought (I know you did from your code). Hence, putting ALL( Stock ) will remove all filters from all related tables (on condition that the other tables are connected to Stock in a 1:many fashion, which I think is the case here). To know how the ALL* functions work you need to know the theory of expanded tables, by the way.
So, in order to get what you want you have to write:
calculate (
[Measure 1], -- please give a meaningful name, Measure 1 means NOTHING
all ( Stock ), -- remove all filters
'Calendar Date Detail' -- restore the filters on Calendar Date Datail only
)
Best
Darek
- Anonymous7 years agoNot applicable
Hi Darek,
Thank you for your explanation on the expanded table, I will read up on that more. I managed to make my formula work now with the solution suggested above. Measure 1 and 2 are only due to anonymization of business sensitive data, I would never call a measure that in real llife :)
Thank you for your help!