How to display multiple true conditions

[NewinPBi]

Hi, I am new in PBi and struggling with multiple TRUE condition display

My table

Name   Age     Sex            Type        Province         DxYear

Andy    24        M              2             12                    2003

Lisa      45        F               3              13                    2004

Hanay  55       M               1              14                   2005

I want to add a New Column "Edits" and want to display information as a group in this column based on conditions

Edits =

//Edit1 IF (NOT('Tumours'[province]=10),"Edit1",

//Edit2 IF('Tumours'[type ] = 1), "Edit2",

//Edit3 IF('Tumours'[DxYear] < 2004", "Edit3",

"No Edit" )))

I want final display like this

Name            Age           Sex            Type            Province   DxYear           Edits

Andy            24               M               2                12              2003             Edit1

                                                                                                                   Edit2

                                                                                                                   Edit3

Lisa              45               F                 3               10                2002            Edit3

Hany            55              M                1               14                 2005           Edit1

                                                                                                                  Edit2

If I use IF statement then Power Bi only checks First TRUE condition, but one record may have multiple TRUE condition and I want to display all TRUE conditions in one group. 

Is there looping in PowerBi? Any help would be appreciated

[v-jayw-msft · Accepted solution]

Hi @NewinPBi ,

You may do this in Query Editor using Add Conditional Column and Unpivot Columns feature.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLqVTSUTIyARK+IAYQG4IIIwMDY6VYnWgln8ziRCDfxBRIuAGxMUiFMUSFCViFR2JeIsgQU1OoIYYgbAJRYqoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Sex = _t, Type = _t, Province = _t, DxYear = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Sex", type text}, {"Type", Int64.Type}, {"Province", Int64.Type}, {"DxYear", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",13,10,Replacer.ReplaceValue,{"Province"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",2004,2002,Replacer.ReplaceValue,{"DxYear"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Province] <> 10 then "edit1" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Type] = 1 then "edit2" else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [DxYear] < 2004 then "edit3" else null),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Conditional Column", {"Name", "Age", "Sex", "Type", "Province", "DxYear"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Alexander76877 · Accepted solution]

Hi, loops are called "iterators".

You need CONCATENATEX.

Create a sample table using

Table = GENERATESERIES(1, 10, 1)

Renaming the column "value" by "nr".

Create a column "edit" by using 

Edit =

CONCATENATEX(

GENERATESERIES(1, 3, 1),

SWITCH([Value],

1, IF('Table'[Nr]= 5, "Edit1

"),

2, IF('Table'[Nr]>2, "Edit2

"),

3, IF('Table'[Nr]<8, "Edit3

"), BLANK()

)

)

Make a table visual displaying "nr" (don´t summarize) and "edit".

That´s the result you wanted.

Alexander

[v-jayw-msft · Accepted solution]

Hi @NewinPBi ,

You may do this in Query Editor using Add Conditional Column and Unpivot Columns feature.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsxLqVTSUTIyARK+IAYQG4IIIwMDY6VYnWgln8ziRCDfxBRIuAGxMUiFMUSFCViFR2JeIsgQU1OoIYYgbAJRYqoUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Age = _t, Sex = _t, Type = _t, Province = _t, DxYear = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Age", Int64.Type}, {"Sex", type text}, {"Type", Int64.Type}, {"Province", Int64.Type}, {"DxYear", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",13,10,Replacer.ReplaceValue,{"Province"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",2004,2002,Replacer.ReplaceValue,{"DxYear"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each if [Province] <> 10 then "edit1" else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Type] = 1 then "edit2" else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if [DxYear] < 2004 then "edit3" else null),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Conditional Column", {"Name", "Age", "Sex", "Type", "Province", "DxYear"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
    #"Removed Columns"

Pbix as attached.

Best Regards,

Jay

Community Support Team _ Jay Wang

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