ALLEXCEPT not returning values as expected

[GlenntB]

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?

[v-danhe-msft · Accepted solution]

Hi @GlenntB,

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

[LivioLanzo]

@GlenntB

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"
    }
)

[GlenntB]

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?

[LivioLanzo]

Do you have an active relationship between the two tables ?

[GlenntB]

Yes there is an active one(Date) to many(UrenOmzet) relationship

[LivioLanzo]

THe filter on date should stay, maybe you can share your pbix file