Measure with condition for multiple blanks in column

[meyerliu]

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	2020	P2	99.98%
App1	2020	P1	90.10%
App1	2020	P2	90.84%
App2	2020	P3	99.80%
App3	2020	P1	99.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:

Apps	Period_1	Period_2	Period_3
App1	99.10%	90.84%	99.99%
App2	99.99%	99.98%	99.98%
App3	99.45%	99.95%	99.95%
App4	99.9%	99.9%	99.9%

Thanks much in advance for your help and guidance.

[v-yingjl · Accepted solution]

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.