ALLEXCEPT entire table

[mshparber]

I am trying to build a measure that removes filters from any table, except for Calendar. I try to do:

= CALCULATE([Sales Amt], ALLEXCEPT(Calendar)

but it requires specific columns. 
Maybe this should work?

=CALCULATE([Sales Amt], ALL(), VALUES(Calendar))

or does VALUES also require columns?

[CNENFRNL · Accepted solution]

In CALCULATE(), tables can be used as filter modifiers; thus you can author DAX formula this way,

= CALCULATE([Sales Amt], ALL(), Calendar)

[iBusinessBI]

Thanks!

I've checked and both syntaxes work:

=CALCULATE([Sales Amt], ALL(), VALUES(Calendar))

=CALCULATE([Sales Amt], ALL(), Calendar)

So, what does the values do if used on the whole table?

[CNENFRNL]

When a table name is given, VALUES(tablename) returns a table with the same columns and all the rows of the table (including duplicates) with the additional blank row if present.

VALUES – DAX Guide

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I assume your fact table and a calendar table are connected via [Date] column.

Please try the below measure.

new measure: =
CALCULATE (
    [Sales Amt],
    ALLEXCEPT ( 'Your fact table', 'Your fact table'[Date] )
)

[mshparber]

No, this will not work.
It is not how it works in data models in Power BI