Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tgjones43
Helper IV
Helper IV

Count and rank values across multiple columns

Hi all

 

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

 

Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9Column 10Required Column
AAAAAAAAAAA
AAABAAAABAA
BAABAAAABAA,B
BBBABBAABBB,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?

 

1 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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

13 REPLIES 13
Zubair_Muhammad
Community Champion
Community Champion

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

 

 


Regards
Zubair

Please try my custom visuals

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

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

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad  that works perfectly, thank you so much!

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

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]
    ,
    ",")

 

 

 


Regards
Zubair

Please try my custom visuals

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.

@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]
    ,
    ",")

Regards
Zubair

Please try my custom visuals

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

@tgjones43 

 

Here is the sample file


Regards
Zubair

Please try my custom visuals

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

@Zubair_Muhammad 's solution is much more elegant 🙂

Anonymous
Not applicable

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"
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.