Forum Discussion
ALLEXCEPT not returning values as expected
Hi,
I'm trying to create a measure that returns the total cost of all rows in a timesheet table in a given period. I created a date table that connects to the timesheet table (UrenOmzet) on the date of the rows.
SUMX(
FILTER(
ALLEXCEPT(UrenOmzet;'Date'[Date]);
UrenOmzet[activiteit_oid] <> "0C31547C-BA9D-474B-87DD-F52F2825B31B" && UrenOmzet[activiteit_oid] <> "FDF01697-B35A-47AF-A91D-CFD4F2BA6C2E"
);
UrenOmzet[Uren- geschreven] * UrenOmzet[Kostprijs - per uur]
)For some reason the ALLEXCEPT function does not seem to work on the date value. Any ideas on what could be the problem here?
Hi Anonymous,
Based on my test, you could refer to below formula:
Sample data:
Create a measure:
b = CALCULATE(SUMX('Table1',[NUMBER]*[WEIGHTED]), FILTER(ALLEXCEPT('Table1','Table1'[Date]), [Item] <> "C" && Table1[WEIGHTED] <> 63))Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
6 Replies
Anonymous
ALLEXCEPT is meant to be used more a filter argument than as a table
try like this:
= CALCULATE ( SUMX ( UrenOmzet, UrenOmzet[Uren- geschreven] * UrenOmzet[Kostprijs - per uur] ), ALLEXCEPT ( UrenOmzet, Date ), NOT UrenOmzet[activiteit_oid] IN { "0C31547C-BA9D-474B-87DD-F52F2825B31B", "FDF01697-B35A-47AF-A91D-CFD4F2BA6C2E" } )This leaves me with the same problem. It seems as if the date table does not filter the dates in the UrenOmzet table whenever I filter the date table on year or month. I did mark my date table as a date table, but the hierarchy does not seem to work as expected. Is there a known solution to this problem?
Do you have an active relationship between the two tables ?
Hi Anonymous,
Based on my test, you could refer to below formula:
Sample data:
Create a measure:
b = CALCULATE(SUMX('Table1',[NUMBER]*[WEIGHTED]), FILTER(ALLEXCEPT('Table1','Table1'[Date]), [Item] <> "C" && Table1[WEIGHTED] <> 63))Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
