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!
Does this give you the right answer?
Measure 2 = CALCULATE( [Measure 1], ALLEXCEPT( Stock, 'Calendar Date Detail', Branch ) )Best
D.