Creating a Counter Column

[aa_KF]

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.

[ChrisMendoza · Accepted 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!

[aa_KF]

@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!

[v-frfei-msft · Accepted solution]

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.

[ChrisMendoza · Accepted 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!

[mussaenda]

Counter = (CONTAINSSTRINGEXACT(Table1[col1],"Yes")) + (CONTAINSSTRINGEXACT(Table1[col2],"Yes")) + (CONTAINSSTRINGEXACT(Table1[col3],"Yes"))

I don't know if this is right but it works

[jdbuchanan71]

Try this

Counter = IF (Table1[Col1] = "yes",1,0) + IF (Table1[Col2] = "yes",1,0) + IF (Table1[Col3] = "yes",1,0)