cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

How to display multiple true conditions

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to display multiple true conditions

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"

4.PNG

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Helper II
Helper II

Re: How to display multiple true conditions

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: How to display multiple true conditions

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"

4.PNG

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

Highlighted
Helper II
Helper II

Re: How to display multiple true conditions

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

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors