Forum Discussion
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 should work?
=CALCULATE([Sales Amt], ALL(), VALUES(Calendar))
or does VALUES also require columns?
In CALCULATE(), tables can be used as filter modifiers; thus you can author DAX formula this way,
= CALCULATE([Sales Amt], ALL(), Calendar)
5 Replies
In CALCULATE(), tables can be used as filter modifiers; thus you can author DAX formula this way,
= CALCULATE([Sales Amt], ALL(), Calendar)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?
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.
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] ) )No, this will not work.
It is not how it works in data models in Power BI
