SWITCH with 3 conditions based on column gives "random" result

[Anonymous]

Hi,

Could someone please help, I can't figure out what is the issue with my DAX.

I have a "Työaika" table and I'm trying to make calculated column "FTE" but it gives random results. With two statements it works fine both, using switch or if but adding the third condition it gets messed up. for example here is a picture from table and wrong results

My DAX statement:

OsaAika FTE =

SWITCH(True(),

'Työsopimus'[Työsuhteen muoto]="Ei määritetty" && 'Työsopimus'[startdate] <= TODAY() || 'Työsopimus'[enddate] >= TODAY() || BLANK(),0,

'Työsopimus'[Työsuhteen muoto]="Kokoaikainen" && 'Työsopimus'[startdate] <= TODAY() || 'Työsopimus'[enddate] >= TODAY() || BLANK(),1,

'Työsopimus'[Työsuhteen muoto]="Osa-aikainen" && 'Työsopimus'[startdate] <= TODAY() || 'Työsopimus'[enddate] >= TODAY() || BLANK(),

CALCULATE(

SUMX('Työsopimus','Työsopimus'[weeklyworktimemaxminutes])/2/37.5

)

)

[v-jayw-msft]

Hi @Anonymous ,

Your formula involves multiple AND()/OR() conditions. In order to ensure the correctness of the formula, can you describe the logic?

Also, please show the expected result (what is the correct result where you marked) so that we can understand your logic.

Best Regards,

Jay

[Anonymous]

https://drive.google.com/drive/folders/1ezZPjDyCdk7tzaSF6xkXue_DqnWIMEwY?usp=sharing

here is my pbix file, if it helps

[Samarth_18]

Hi @Anonymous ,

Please try this:-

OsaAika FTE =
SWITCH (
    TRUE (),
    (
        'Työsopimus'[Työsuhteen muoto] = "Ei määritetty"
            && 'Työsopimus'[startdate] <= TODAY ()
    )
        || ( 'Työsopimus'[enddate] >= TODAY () )
        || ( BLANK () ), 0,
    (
        'Työsopimus'[Työsuhteen muoto] = "Kokoaikainen"
            && 'Työsopimus'[startdate] <= TODAY ()
    )
        || ( 'Työsopimus'[enddate] >= TODAY () )
        || ( BLANK () ), 1,
    (
        'Työsopimus'[Työsuhteen muoto] = "Osa-aikainen"
            && 'Työsopimus'[startdate] <= TODAY ()
    )
        || ( 'Työsopimus'[enddate] >= TODAY () )
        || ( BLANK () ),
        CALCULATE (
            SUMX ( 'Työsopimus', 'Työsopimus'[weeklyworktimemaxminutes] ) / 2 / 37.5
        )
)

[Anonymous]

Hi @Samarth_18 and thank you for your reply. This didn't help. It gives the same result. To mention, I have tried this to other column too and the end result is the same.