DAX Misbehaves for Total Calculations using ALL and Text Filter

[sabariprabu]

Hi,

I have created below measure to get the sum of ActualBudgetDeviation by removing all the filters applied in ResourceHierarchy[ResourceHierarchyCol1] and then applying only one filter which is first 2 characters of the string should not start with "01".

ActualBudgetDeviationALL := CALCULATE([ActualBugetDeviation],ALL(ResourceHierarchy[ResourceHierarchyCol1]),LEFT(ResourceHierarchy[ResourceHierarchyCol1],2)<>"01")

But this DAX does not work. It is giving me the value for all the fields in ResourceHierarchy[ResourceHierarchyCol1] and the LEFT(ResourceHierarchy[ResourceHierarchyCol1],2)<>"01" condition does not works.

Please help.

[Fowmy · Accepted solution]

@sabariprabu 

Add filter as follows:

ActualBudgetDeviationALL :=
CALCULATE (
    [ActualBugetDeviation],
    FILTER (
        ALL ( ResourceHierarchy[ResourceHierarchyCol1] ),
        LEFT ( ResourceHierarchy[ResourceHierarchyCol1], 2 ) <> "01"
    )
)