Summarize, Filter & LookupValue

DAX DAX Calculations
1

Hi, I am having trouble with the below Dax calc which I am running through Dax Studio to test:

EVALUATE ADDCOLUMNS(
    SUMMARIZE(
        Group_GLE,
        Group_GLE[Entity_Curr],
        Group_GLE[Period]
    ),
    "Entity_Value", CALCULATE(
        SUM( Group_GLE[Entity_Value] ),
        FILTER(
            Group_COA,
            Group_COA[AccountNumber] <> 4150 &&
            Group_COA[AccountNumber] <> 5150
        )
    ),
    "Group_EXRate", (
        LOOKUPVALUE(
            Group_EXRates[ER],
            Group_EXRates[Period],
            Group_GLE[Period],
            Group_EXRates[To_Curr],
            "GBP"
        ) / LOOKUPVALUE(
            Group_EXRates[ER],
            Group_EXRates[Period],
            Group_GLE[Period],
            Group_EXRates[To_Curr],
            Group_GLE[Entity_Curr]
        )
    )
)

The problem is the filter for the “Entity_Value” section, if i remove the filter the results are correct but when i put the filter in which i need the results do not work.

Any help on the filter and the overall Dax in case there are other efficiencies i should adopt would be very much appreciated.

Below is a copy of the table that Dax Studio produces to give you a better ide aof what i am doing:

image

Thanks

2

Finally managed to work out the solution which is below for anyone interested:

SUMX(
    Calculatetable(
        ADDCOLUMNS(
            SUMMARIZE(
                Group_GLE,
                Group_GLE[Entity_Curr],
                Group_GLE[Period]
            ),
            "Ent_Value", ( [Amount Entity] ),
            "EXRate", (
                lookupvalue(
                    Group_EXRates[ER],
                    Group_EXRates[Period],
                    Group_GLE[Period],
                    Group_EXRates[To_Curr],
                    SelCurrency
                ) / lookupvalue(
                    Group_EXRates[ER],
                    Group_EXRates[Period],
                    Group_GLE[Period],
                    Group_EXRates[To_Curr],
                    Group_GLE[Entity_Curr]
                )
            )
        ),
        Group_COA[AccountNumber] <> 4150 &&
        Group_COA[AccountNumber] <> 5150
    ),
    [Ent_Value] * [ExRate]
)