cancel
Showing results 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

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

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

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:

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

1 ACCEPTED SOLUTION
Super User

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?

9 REPLIES 9
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....

Super User

Hi @MRHUP

try adding it as a measure

SELECTEDVALUE ( Table[Id] )

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:

Frequent Visitor

Maybe a better picture of this outcome:

Super User

Are you having the measures on rows or columns?

Frequent Visitor

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

Super User

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?

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:

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!

Super User

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