Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I want to create new column that acts as a Counter for col1, col2, col3. It will count the number of "Yes" values in each row, starting from col1 to col3 values.
Sample Table:
Name | col1 | col2 | col3
James | Yes | | Yes
Alex | No | No | Yes
John | Yes | Yes | Yes
George | No | Yes |
Desired Column:
Name | col1 | col2 | col3 | Counter
James | Yes | | Yes | 2
Alex | No | No | Yes | 1
John | Yes | Yes | Yes | 3
George | No | Yes | | 1
Thank you and most appreciated.
Solved! Go to Solution.
@aa_KF -
More of an exercise in trying new things (for me):
I took your sample and unpivoted your [col1],[col2], and [col3] in the Query Editor.
Create a [CountYes] measure as:
CountYes = CALCULATE( COUNTROWS(Table1), Table1[Value] = "Yes" )
Then I used https://www.sqlbi.com/articles/using-concatenatex-in-measures/ as an example/template to create a [Counter Measure] as:
Counter Measure = VAR TotalYes = [CountYes] VAR NameYes = ADDCOLUMNS ( VALUES ( Table1[Name] ), "Yes's", [CountYes] ) RETURN IF ( ISFILTERED ( Table1[Name] ), IF ( TotalYes > 0, CONCATENATEX ( SELECTCOLUMNS ( TOPN ( 1, NameYes, [Name] ), "Result", [Yes's] ), [Result] ) ), FORMAT([CountYes],"0") )
Producting a Matrix visual as:
Thank you for the inspiration to try new things!
Proud to be a Super User!
Hi @aa_KF ,
Please check the following steps as below.
1, Unpivot the table in power query as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lGKBJNQRqxOtJJjDljELx9GwCS88jPy4BoQJEjKPTW/KD0VpgEmEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, col1 = _t, col2 = _t, col3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"col1", type text}, {"col2", type text}, {"col3", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value") in #"Unpivoted Other Columns"
2. Create a measure based on the new table.
Measure = IF ( ISFILTERED ( 'Table'[Attribute] ), MAX ( 'Table'[Value] ), CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Value] = "Yes" ) ) )
3. Then we can get the result by creating a matrix.
@jdbuchanan71 @mussaenda @ChrisMendoza @v-frfei-msft Thank you a lot all!! I really appreicate it.
I'm going to try them all.
Thank you again!
Hi @aa_KF ,
Please check the following steps as below.
1, Unpivot the table in power query as below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lGKBJNQRqxOtJJjDljELx9GwCS88jPy4BoQJEjKPTW/KD0VpgEmEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [name = _t, col1 = _t, col2 = _t, col3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"name", type text}, {"col1", type text}, {"col2", type text}, {"col3", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"name"}, "Attribute", "Value") in #"Unpivoted Other Columns"
2. Create a measure based on the new table.
Measure = IF ( ISFILTERED ( 'Table'[Attribute] ), MAX ( 'Table'[Value] ), CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Value] = "Yes" ) ) )
3. Then we can get the result by creating a matrix.
@aa_KF -
More of an exercise in trying new things (for me):
I took your sample and unpivoted your [col1],[col2], and [col3] in the Query Editor.
Create a [CountYes] measure as:
CountYes = CALCULATE( COUNTROWS(Table1), Table1[Value] = "Yes" )
Then I used https://www.sqlbi.com/articles/using-concatenatex-in-measures/ as an example/template to create a [Counter Measure] as:
Counter Measure = VAR TotalYes = [CountYes] VAR NameYes = ADDCOLUMNS ( VALUES ( Table1[Name] ), "Yes's", [CountYes] ) RETURN IF ( ISFILTERED ( Table1[Name] ), IF ( TotalYes > 0, CONCATENATEX ( SELECTCOLUMNS ( TOPN ( 1, NameYes, [Name] ), "Result", [Yes's] ), [Result] ) ), FORMAT([CountYes],"0") )
Producting a Matrix visual as:
Thank you for the inspiration to try new things!
Proud to be a Super User!
Counter = (CONTAINSSTRINGEXACT(Table1[col1],"Yes")) + (CONTAINSSTRINGEXACT(Table1[col2],"Yes")) + (CONTAINSSTRINGEXACT(Table1[col3],"Yes"))
I don't know if this is right but it works
Try this
Counter = IF (Table1[Col1] = "yes",1,0) + IF (Table1[Col2] = "yes",1,0) + IF (Table1[Col3] = "yes",1,0)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
94 | |
80 | |
71 | |
64 |
User | Count |
---|---|
115 | |
106 | |
96 | |
81 | |
72 |