Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Fantmas
Helper III
Helper III

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
    )

 

Fantmas_1-1684822569025.pngFantmas_2-1684822872513.png

 

1 REPLY 1
some_bih
Super User
Super User

Hi @Fantmas if not already, check article https://www.sqlbi.com/articles/blank-handling-in-dax/ .

Keep in mind how DAX engine treat blank in functions like AVERAGE, link https://learn.microsoft.com/en-us/dax/average-function-dax .

It could be that you need to use AVERAGEX function. I hope this help.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.