Forum Discussion

mshparber's avatar
mshparber
Icon for Advocate I rankAdvocate I
4 years ago
Solved

ALLEXCEPT entire table

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 sho...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago

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

    = CALCULATE([Sales Amt], ALL(), Calendar)
iBusinessBI's avatar
iBusinessBI
Icon for Kudo Collector rankKudo Collector
4 years ago

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's avatar
CNENFRNL
Icon for Community Champion rankCommunity Champion
4 years ago
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