IF else measure return 1 ,0 and blank

[NickProp28]

Dear community,

Any hints would be appreciated, I am trying my best to create this measure.

This is a condition that I'd like to convert into a measure.

Condition:

i.) When there is no flight time for direction 'APAC' AND the first character in the code is 'V', will return '0', otherwise '1'.But other than code start with 'V', with or without date, will return blank.

ii.) When there is no flight time for direction 'AMER', will return '0'

iii.) Direction 'EMEA' , with or without time flight, will return blank

Expected result:

Attached with pbix: https://drive.google.com/file/d/1PEkk4PX37H4w8_1c6Dcl_TpEUa8IpEmW/view?usp=sharing

Appreciate any helps provided ! Thank you!

[Anonymous · Accepted solution]

Hi @NickProp28 ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR shouzi =
    LEFT ( MAX ( FlightTable[Code] ), 1 )
RETURN
    IF (
        MAX ( FlightTable[Direction] ) = "APAC"
            && MAX ( FlightTable[Flight Time] ) = BLANK ()
            && shouzi = "V",
        0,
        IF (
            MAX ( FlightTable[Direction] ) = "APAC"
                && MAX ( FlightTable[Flight Time] ) <> BLANK ()
                && shouzi = "V",
            1,
            IF (
                MAX ( FlightTable[Direction] ) = "AMER"
                    && MAX ( FlightTable[Flight Time] ) <> BLANK (),
                1,
                IF (
                    MAX ( FlightTable[Direction] ) = "AMER"
                        && MAX ( FlightTable[Flight Time] ) = BLANK (),
                    0,
                    IF ( MAX ( FlightTable[Direction] ) = "EMEA", BLANK () )
                )
            )
        )
    )

Or a column.

Column =
VAR shouzi =
    LEFT ( ( FlightTable[Code] ), 1 )
RETURN
    IF (
        ( FlightTable[Direction] ) = "APAC"
            && ( FlightTable[Flight Time] ) = BLANK ()
            && shouzi = "V",
        0,
        IF (
            ( FlightTable[Direction] ) = "APAC"
                && ( FlightTable[Flight Time] ) <> BLANK ()
                && shouzi = "V",
            1,
            IF (
                ( FlightTable[Direction] ) = "AMER"
                    && ( FlightTable[Flight Time] ) <> BLANK (),
                1,
                IF (
                    ( FlightTable[Direction] ) = "AMER"
                        && ( FlightTable[Flight Time] ) = BLANK (),
                    0,
                    IF ( ( FlightTable[Direction] ) = "EMEA", BLANK () )
                )
            )
        )
    )

Best Regards

Community Support Team _ Polly

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

[Chew_WenJie]

Hello, you can use below measure to get the expected result table.

Measure = 
var direction = SELECTEDVALUE(FlightTable[Direction])
var code = LEFT(SELECTEDVALUE(FlightTable[Code]),1)
var flightTime = NOT(ISBLANK(SELECTEDVALUE(FlightTable[Flight Time]))) 

return
IF(direction="APAC"&&code="V"&&flightTime=FALSE(),0,
IF(direction="APAC"&&code="V"&&flightTime=TRUE(),1,
IF(direction="AMER"&&flightTime=TRUE(),1,
IF(direction="AMER"&& flightTime=FALSE(),0,
IF(code="V",BLANK(),
IF(direction="EMEA",BLANK()
))))))

Hope this able to help you.