Forum Discussion

meyerliu's avatar
meyerliu
Frequent Visitor
6 years ago
Solved

Measure with condition for multiple blanks in column

Hello.
 
I'm having trouble creating a measure using IF, ISBLANK, and also trying to throw in FILTER in the mix that does not work.  
 
With the data, I calculated the Availability percentage first:
==> P1_Availability = CALCULATE(AVERAGE(Data[Availability]),FILTER(Data,Data[Year]="2020"), FILTER(Data,Data[Period]="P1"))
 
Data:
Apps     Year   Period   Availability (calculated col)
App2                2020P2 99.98%
App12020P190.10%
App12020P290.84%
App22020P399.80%
App32020P199.45%
 
When I have the final result of P1_Availability, I have the following simple condition that I need to expand:
==> Period_1 = IF(ISBLANK(Data[P1_Availability]),"99.99%", Data[P1_Availability])
 
The trouble I'm having is this will only list "99.99%" if the average is Blank for any Apps.
The additional condition is to list "99.99%" if App1 and App2 are Blank, "99'95% if App3 is Blank, and "99.9%" if App4 is Blank.
 
Outcome:
AppsPeriod_1Period_2Period_3
App199.10%90.84%99.99%
App299.99%99.98%99.98%
App399.45%99.95%99.95%
App499.9%99.9%99.9%
 
Thanks much in advance for your help and guidance.
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi meyerliu ,

    Try like these measures:

     

    Period_1 =
VAR _Average =
    CALCULATE (
        AVERAGE ( 'Data'[Availablity] ),
        FILTER ( 'Data', 'Data'[Year] = 2020 && 'Data'[Period] = "P1" )
    ) * 100
RETURN
    IF (
        _Average <> BLANK (),
        _Average & "%",
        IF ( SELECTEDVALUE ( Data[Apps] ) = "App2", "99.99%", "99.9%" )
    )
    Period_2 =
VAR _Average =
    CALCULATE (
        AVERAGE ( 'Data'[Availablity] ),
        FILTER ( 'Data', 'Data'[Year] = 2020 && 'Data'[Period] = "P2" )
    ) * 100
RETURN
    IF (
        _Average <> BLANK (),
        _Average & "%",
        IF ( SELECTEDVALUE ( Data[Apps] ) = "App3", "99.95%", "99.9%" )
    )
    Period_3 =
VAR _Average =
    CALCULATE (
        AVERAGE ( 'Data'[Availablity] ),
        FILTER ( 'Data', 'Data'[Year] = 2020 && 'Data'[Period] = "P3" )
    ) * 100
RETURN
    IF (
        _Average <> BLANK (),
        _Average & "%",
        IF (
            SELECTEDVALUE ( Data[Apps] ) = "App1",
            "99.99%",
            IF ( SELECTEDVALUE ( Data[Apps] ) = "App3", "99.95%", "99.9%" )
        )
    )

     

     

    Sample file is attached that hopes to help you, please check and try it: Measure with condition for multiple blanks in column.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi meyerliu ,

    Try like these measures:

     

    Period_1 =
VAR _Average =
    CALCULATE (
        AVERAGE ( 'Data'[Availablity] ),
        FILTER ( 'Data', 'Data'[Year] = 2020 && 'Data'[Period] = "P1" )
    ) * 100
RETURN
    IF (
        _Average <> BLANK (),
        _Average & "%",
        IF ( SELECTEDVALUE ( Data[Apps] ) = "App2", "99.99%", "99.9%" )
    )
    Period_2 =
VAR _Average =
    CALCULATE (
        AVERAGE ( 'Data'[Availablity] ),
        FILTER ( 'Data', 'Data'[Year] = 2020 && 'Data'[Period] = "P2" )
    ) * 100
RETURN
    IF (
        _Average <> BLANK (),
        _Average & "%",
        IF ( SELECTEDVALUE ( Data[Apps] ) = "App3", "99.95%", "99.9%" )
    )
    Period_3 =
VAR _Average =
    CALCULATE (
        AVERAGE ( 'Data'[Availablity] ),
        FILTER ( 'Data', 'Data'[Year] = 2020 && 'Data'[Period] = "P3" )
    ) * 100
RETURN
    IF (
        _Average <> BLANK (),
        _Average & "%",
        IF (
            SELECTEDVALUE ( Data[Apps] ) = "App1",
            "99.99%",
            IF ( SELECTEDVALUE ( Data[Apps] ) = "App3", "99.95%", "99.9%" )
        )
    )

     

     

    Sample file is attached that hopes to help you, please check and try it: Measure with condition for multiple blanks in column.pbix 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.