Matrix Visual ... Sort by column which i don't want to show

[vmonu]

Good day all,

I have table say

Column 1                Name ...... other fields

00001-A                   A

00001-B                   B

00002-A                   A

So when i do a Matrix Visual .

I just want to show ONLY Name column (sorted) which it does but it will show A both for 00001-A & 00002-A values.

So when i try add the Column 1 as sort by ... it works 

But i don't want that column  to show in the ROW HEADER... 

Any way to sort by or hide that value  alone

Thanks

MN

[Anonymous]

Hi @vmonu 

If your use the same column header like A, the result will aggregate.

So we need to make the column header be different.

Test table:

Power Query Editor:

Add two duplicate column(ID and Name), then merge two colums.

Result:

Dax:

Calculated column

Dax = 'Table'[Name]&"("&'Table'[ID]&")"

Let Merge and Dax column sort by Index column.

 Then build a Matrix visual(Select show on rows in values, turn off word wrap in Row headers, drag the line in visual to reduce the size of Row header to 0).

Result:

You can download the pbix file from this link: Matrix Visual ... Sort by column which i don't want to show

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[vmonu]

@Anonymous , unfortuantely ,

How do i hide the first line .. the row header doesn't allow me to make it to ZERO or doesn't even bring it reduce the size to make it invisible . that's the problem. 

Do i make sense... see the one in Yellow . i don't want them to show at all 

Thanks

[Anonymous]

Hi @vmonu 

You may change the color of column header to be white.

Result:

Or you can build a rectangle in same length size to cover the column header.

Move the rectangle to cover the column header, select two visual by ctrl + left click and group two visuals.

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[vmonu · Accepted solution]

@Anonymous 

Yep smart idea...it worked. ... credit to you... but let me sum it for others as the solution involves 2 messages from Rico but i got to tweak it litle bit.

1)  I brought both the columns to the COLUMN level in the MATRIX

2) The sort happened on the 1st column (i..e. 00001-A ) but i renamed the column to dot or comma (i.e. . or , ) so it won't show in the header

3) But the values were showing up like 00001-A, 00001-B, 00002-A also in the column header and below that the proper header like  A, B, A were showing up

4) To avoid that .. i used Rico 2nd idea... created a Rectangle (empty) one and made color white ... and laid it on the top of the 1st column header... Volla .. now it will loook like only the A, B, A header shown.

Thanks again... amazing idea.

Best 

MN

[amitchandak]

@vmonu , You can define sort column for a column. Refer

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

[vmonu]

@amitchandak ,  Thanks yes i am aware... but 

when you do a  MATRIX VISUAL ( not regualr table column) 

it always sorts by the 1st column , you select.

Please see message 2  and i posted .. what is coming and what i am expecting.

Thanks

MN

[amitchandak]

@vmonu , If you want to sort on a column which is not present. I doubt that is possible.

Hope I got it correctly 

[vmonu]

Dang is there a way i can even hide it in the COLUMN header ...

Let's wait and see... and there are some visuals that allow to sort by TOOLTIPS and anot show.

Let me wait and see.. Thanks

[vmonu]

In other words..

i want to show like this

                 A                                          B                                            A 

             <values for 00001-A>        < values for 0001-B.>            < values for 00002-A>

Right now it's showing

                          A                                          A                                            B

<values for 00001-A>        < values for 0002-A.>            < values for 00002-A>

Hope it helps

Thanks