Forum Discussion

Fantmas's avatar
Fantmas
Helper III
3 years ago

Filter Blank values in a division without using filter Panel

 

 

Hi All,

I have created a DAX expression to calculate an average based on a division, my issue is to have the correct figures I need to remove from the calculation blank value, the only Solution that I found so far it is to use the filter pane and I would like to know if I can filter the value inside my DAX expression :

Test _ WH_Mesure =

VAR Numerator =
    CALCULATE(
        AVERAGE('Fact Table'[Value]),
        FILTER(ALL(DimWHAttribute[Attribute]), DimWHAttribute[Attribute] = "Attribute A"
    )
)

VAR Denominator =
    CALCULATE(
        AVERAGE('Fact Table'[Value]),
        FILTER(ALL(DimWHAttribute[Attribute]), DimWHAttribute[Attribute] = "Attribute B"
    )
)

VAR DivisionResult =
    DIVIDE(Numerator, Denominator, 0)

RETURN
    SWITCH(
        TRUE(),
            ISBLANK(DivisionResult) || DivisionResult = 0 || ISBLANK(Numerator) || ISBLANK(Denominator),
            BLANK(),
            DivisionResult
    )