Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
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 )
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 )
@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?
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 )
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] , ",")
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.
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] , ",")
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.
Here is the sample file
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"
)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |