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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MRHUP
Frequent Visitor

How to place an extra column in a matrix (based on a string)?

Hi,

 

For an assignment I am looking for a solution, hopefully it is possible.... In a Matrix, after a regular count of the number of some  occurrences, I want to add an extra column for clarification for the end user. These are strings that come before the number (the 'main figures'): "A1" to "A16", categories so to speak.

 

My initial matrix looks like the following (with figures in every cell, unlike the sample below):

 

Screenshot 1.png

 

 

 

 

 

 

Now I want to add the extra column, in order to get something like this (sample made in Excel, without the Totals):

 

Screenshot 2.png

 

 

 

The Id's (A1 to A16) are based on the combination of the x and y axis (like: Car and Blue = A3), and are situated in the model as a calculated column:

 

Id = 
SWITCH(TRUE(),
TEST'[X]="Blue" && 'TEST'[Y]="Bike","A1",
TEST'[X]="Blue" && 'TEST'[Y]="Bus","A2",
TEST'[X]="Car" && 'TEST'[Y]="Blue","A3",
TEST'[X]="Plane" && 'TEST'[Y]="Blue","A4",
TEST'[X]="Brown" && 'TEST'[Y]="Bike","A5",
TEST'[X]="Brown" && 'TEST'[Y]="Bus","A6",
etc.

 

 

The thing is, no matter what I try, I can't get this extra column added in the Matrix. It is simply possible to add an extra row using these Id's (without a column name then, but frankly that doesn't matter much), but then the ID's are in a self-contained row, not in front of the number, see below:

 

Screenshot 3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Does anyone have any ideas? Many thanks in advance for your thoughts!

1 ACCEPTED SOLUTION

@MRHUP 

Sure. But from the format settings make sure you don't select "measure on rows" and you have (compared to the given example) the category on rows and the color on columns. Also what are the empty rows that is shown in the last screenshot?

View solution in original post

9 REPLIES 9
MRHUP
Frequent Visitor

Ha yes I know, the allignment isn't that hard, also blanked out the total for the A1 - A16 fields (this total doesn't make any sense). But I expected the full A1 to A16 in the matrix (as in the example in the first post). Played around a bit, and the 'trick' for this was; use the same measures / columns as used in the matrix itself... 😉 I used a different column in the measure, this column contains the color codes. The matrix itself then includes the column of the actual colors. And then something doesn't go quite right....

 

Thanks for your help!

tamerj1
Super User
Super User

Hi @MRHUP 

try adding it as a measure 

SELECTEDVALUE ( Table[Id] )

MRHUP
Frequent Visitor

Hi, unfortunately this doesn't work. I only can add this as a value, not as a column or row. Doesn't have to be a problem I think, however, it still produces an extra row:

 

Screenshot 4.png

MRHUP
Frequent Visitor

Maybe a better picture of this outcome:

 

Screenshot 5.png

Are you having the measures on rows or columns?

MRHUP
Frequent Visitor

I was only allowed to add the new measure for the A1 - A16 ID's (SELECTEDVALUE ( Table[Id] )) into the value field.

@MRHUP 

Sure. But from the format settings make sure you don't select "measure on rows" and you have (compared to the given example) the category on rows and the color on columns. Also what are the empty rows that is shown in the last screenshot?

MRHUP
Frequent Visitor

Hi, the empty rows are the result of placing the just made measure as a value (with the option "show on rows"), creating the same as in the example:

Screenshot 3.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Ha, I de-activated the "show on rows", and now it seems pretty nice (see below)! Not exactly what I'd expected (the Id's are located on some odd places), but I have to check the data for this. Will do that tomorrow. Thank you very much for the advice!

 

Screenshot 6.png

@MRHUP 

Yes that is because by default the text data type is aligned left while whole and decimal numbers are aligned right. You can fix that easily. Have a good night

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.