How to use ALL() with a field parameter and return a table of the current selected value?

[Pedro503]

Hey there,

File link here

I'm trying to dynamically return a table based on a slicer selection, as shown in the following image:

What does that means? If, in the field paramenter based slicer, it's selected the DateShortened, then the table will generate a list with all the dates of the calendar dimension table; so for the weeks and the months.

The problem lies in the usage of the ALL() function, because when I try to use the column from the calendar table inside this ALL() filter on the CALCULATE formula, the following message error pops up:

The ALL function expects a table reference expression for argument '1', but a string or numeric expression was used.

Is there any way to make this dynamic? The only workaround I manage to found was using the ISINSCOPE() function, but in my real life dataset I use too many columns that it turns out to be very slow in a performance point of view (because of the CallBackDataID in the SWITCH() formula).

Thanks in advance.

[v-jianboli-msft · Accepted solution]

Hi @Pedro503 ,

Please try:

SalesAmountAll =
SWITCH (
    MAX ( 'P_Date'[P_Date] ),
    "Week", CALCULATE ( [SalesAmount], ALL ( d_calendar[Week] ) ),
    "MonthYear", CALCULATE ( [SalesAmount], ALL ( d_calendar[MonthYear] ) ),
    "DateShortened", CALCULATE ( [SalesAmount], ALL ( d_calendar[DateShortened] ) )
)

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pedro503]

Hey, @amitchandak , do you know whether it's possible to do that?