creating a new column based on filtering from 3 other columns

[Anonymous]

Hi all,

I am relatively new to Power Bi, so the answer to my question may be something simple but I cannot figure it out.

I have a table where I have created 3 separate columns which are showing information based on defined conditions.

Now I would like to create another (4th column) which will be filtered based on the previous columns:

the idea is if the raws in the 3 columns are "-" for the result to be filtered as "-" in the last column. If the values are all"yes", the result to be "yes" and if there is even one "no" the result to be "no".

I tried creating a custom column which did not work. Help would be greatly appreciated.

thanks

Martin

[Anonymous · Accepted solution]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create calculated column.

KPI Overall =
var _yes=IF([Delivery KPI PC]="Yes",1,0)+IF([Delivery KPI Print]="Yes",1,0)+IF([Delivery KPI DJ]="Yes",1,0)
var _no=IF([Delivery KPI PC]="No",1,0)+IF([Delivery KPI Print]="No",1,0)+IF([Delivery KPI DJ]="No",1,0)
return
SWITCH(
    TRUE(),
    _yes+_no=0,"-",
    _no=1,"No",
    _yes=3,"Yes",
    BLANK())

2. Result.

Best Regards,

Liu Yang

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

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I could not know the condition of the result if there is only one yes, so I just flagged it as "check again".

The below is for the column creation.

New Column =

SWITCH (

TRUE (),

'Table'[Delivery KPI PC] = BLANK ()

&& 'Table'[Delivery KPI Print] = BLANK ()

&& 'Table'[Delivery KPI DJ] = BLANK (), BLANK (),

'Table'[Delivery KPI PC] = "yes"

&& 'Table'[Delivery KPI Print] = "yes"

&& 'Table'[Delivery KPI DJ] = "yes", "yes",

'Table'[Delivery KPI PC] = "no"

|| 'Table'[Delivery KPI Print] = "no"

|| 'Table'[Delivery KPI DJ] = "no", "no",

"check again"

)

https://www.dropbox.com/s/9r5h20avss5s2qs/enjoy.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/