Count and rank values across multiple columns

[tgjones43]

Hi all

I have the following data and need a way to create the column at the end (in bold text):

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10	Required Column
A	A	A	A	A	A	A	A	A	A	A
A	A	A	B	A	A	A	A	B	A	A
B	A	A	B	A	A	A	A	B	A	A,B
B	B	B	A	B	B	A	A	B	B	B,A

the measure needs to present the values which occupy 3 or more of the 10 columns, in order of how many times they occur.

So, for row 1, A is the only answer.

For row 2, A is the only answer as B occurs only twice.

For row 3, A occurs 7 times and B 3 times, so A,B is presented.

For row 4, A occurs 3 times and B 7 times, so B,A is presented.

Is this possible?

[Zubair_Muhammad]

@tgjones43 

Here is another way.
This is a calculated column

Column =
VAR temp1 = {
    [Column 1],
    [Column 2],
    [Column 3],
    [Column 4],
    [Column 5],
    [Column 6],
    [Column 7],
    [Column 8],
    [Column 9],
    [Column 10]
}
VAR temp2 =
    ADDCOLUMNS (
        DISTINCT ( temp1 ),
        "MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) )
    )
RETURN
    CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )

[tgjones43]

@Zubair_Muhammadthis works nicely to rank the values, but it needs to be only those values that occupy 3 or more columns. At the moment it gives all values that occur in 1 or more columns. Can the code be modified to do this?

[Zubair_Muhammad · Accepted solution]

@tgjones43 

Sorry I missed that point

Here is the revised code

Column =
VAR temp1 = {
    [Column 1],
    [Column 2],
    [Column 3],
    [Column 4],
    [Column 5],
    [Column 6],
    [Column 7],
    [Column 8],
    [Column 9],
    [Column 10]
}
VAR temp2 =
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( temp1 ),
            "MyCount", COUNTROWS ( FILTER ( temp1, [Value] = EARLIER ( [Value] ) ) )
        ),
        [MyCount] >= 3
    )
RETURN
    CONCATENATEX ( temp2, [Value], ",", [MyCount], DESC )

[tgjones43]

@Zubair_Muhammad  that works perfectly, thank you so much!

[tgjones43]

@Zubair_Muhammad  is it also possible to do this in M?

[Zubair_Muhammad]

Hi @tgjones43 

Here is a custom  column using M which produces same result as DAX

Please see attached file as well

=Text.Combine(
Table.Sort(
Table.SelectRows(
Table.Group(Table.FromList(Record.ToList(_)),"Column1", 
    {{"Count", each Table.RowCount(_), type number}})
    ,
    each [Count]>=3)
    ,
    {{"Count",Order.Descending}})[Column1]
    ,
    ",")

[tgjones43]

Thank you @Zubair_Muhammad  Is it possible to alter the code to ignore other columns. As well as columns 1-10, like in your example, I have 3 other columns. The presence of at least one of these gives an error in the above code.

[Zubair_Muhammad]

@tgjones43 

Give this a shot.

In the Red font are the Column Names to be ignored

let myrecord=Record.RemoveFields(_,{"Column 11","Column 12","Column 13"},1) in
Text.Combine(
Table.Sort(
Table.SelectRows(
Table.Group(Table.FromList(Record.ToList(myrecord)),"Column1", 
    {{"Count", each Table.RowCount(_), type number}})
    ,
    each [Count]>=3)
    ,
    {{"Count",Order.Descending}})[Column1]
    ,
    ",")

[tgjones43]

@Zubair_Muhammad 

Would you mind adding the three extra columns and code to the pbix file and re-attaching it? I am getting an error and it will be easier to work out the problem if I have the file. Thank you.

[Zubair_Muhammad]

@tgjones43 

Here is the sample file

[Zubair_Muhammad]

Yes its very much possible.
I will attempt it later tonight and get back to you

[Anonymous]

@Zubair_Muhammad 's solution is much more elegant 🙂

[Anonymous]

Yes, it's possible, but fairly long.

I'll write a pseudo code:

Colum = 
VAR countOfA=IF(table[Column1]="A";1;0)+IF(table[Column2]="A";1;0)+IF(table[Column2]="A";1;0)+...(etc up to 10)
VAR countOfB=IF(table[Column1]="B";1;0)+IF(table[Column2]="B";1;0)+IF(table[Column2]="B";1;0)+...(etc up to 10)

RETURN
SWITCH(TRUE();
     countOfA<3;"B";
     countOfB<3;"A";
     countOfA>countOfB;"A,B";

    "B,A"
)