Forum Discussion
ALL with ALLEXCEPT
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
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
Community Championwhat do you want to average by? days, regions, stores, something else?
Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).
| Column1 | Column2 |
| A | 1 |
| B | 2.5 |
specifically 'Stock' and 'Branch' tables
Hi Stachu,
I call it an average but it's basically the company total for the time period specified by the date filter, so I'd like it to dynamically re-calculate if the user changes the date range for the report, but always including all values from the Stock and Branch tables regardless of the filters applied to the chart (which will be Regional Manager <> Unknown)
Sample table for Stock:
branch_Skey calendarID Value 1 Value 2 -1 20190815 500 1000 -1 20190816 752 2500 -1 20190817 150 250 65 20190818 150 300 65 20190624 800 900 56 20190625 0 0 181 20190524 500 150 96 20190525 5000 2500 31 20190526 1300 1000 Sample table for Branch:
branch_Skey Branch Short Description Regional Manager -1 Web Unknown 65 Sheffield Manager A 56 London Manager B 181 Stockport Manager C 96 Newcastle Manager A 31 Mobile Unknown Sample table for Calendar:
calendarID relative_date_no 20190815 -3 20190816 -2 20190817 -1 20190818 0 20190624 -55 20190625 -54 20190524 -86 20190525 -85 20190526 -84 Hope this helps and thank you for your help in advance!
- Stachu
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
Your solution calculated a different "company average" per region, as I'm assuming it was still taking into account filters on the Branch table. I amended my formula to this (excuse any formatting issues):
Measure 2 =
CALCULATE(
[Measure 1],
ALLEXCEPT(
'Calendar Date Detail',
'Calendar Date Detail'[relative_date_no]
),
ALLEXCEPT (
'Branch'
),
)And now it's giving me the correct figure. I was just under the impression I'd have to specify that I want to also use ALL the lines from the Stock table as well, but it works without that. Many thanks for your help!