topic Re: Matrix First Column (Rows) Formatting in DAX Commands and Tips

Matrix First Column (Rows) Formatting

BasilRudham — Mon, 07 Nov 2022 06:53:14 GMT

Good Day All, I have an issue with conditionally formatting the first column (Rows) in a matrix. I know most posts/replies state that this cannot be done yet but I had a solution which I got on line in one of the forums but my laptop crashed and I lost the solution. All I basically want is depending on the text value (font colour) in the first column (Rows) to be either Red or Black. What I remember doing was creating a column or measure (cannot remember which one) to specify the values and preferred colour. I then used the DAX function Format to condition the column. This is the bit that I cannot remember how to do. Below is the formula is used for the colour preferences (either column or measure?):

Income Colour = SWITCH(

TRUE(),

tbl_Income_01[Income Statement] = "Gross Margin", "#FF0000",

tbl_Income_01[Income Statement] = "Gross Margin %" , "#FF0000",

tbl_Income_01[Income Statement] = "EBITDA", "#FF0000",

tbl_Income_01[Income Statement] = "EBIT", "#FF0000",

tbl_Income_01[Income Statement] = "EBIT %", "#FF0000",

"#000000"

)

I then used the formula for a measure:

FontColour = FORMAT(tbl_Income_01[Income Statement], tbl_income_01'[Income Colour]) or

ColumnColour = FORMAT(tbl_Income_01[Income Statement],tbl_income_01'[Income Colour])

Neither of these 2 options work. I get the text for the column name on both options but not formatted to the font colours I want. Don't know if a need a MAX/MIN aggragate type function in the formula (it is a text column), I think I did use some funtion but cannot remember which one. I definitely had it working as per the above column/measure used as the format_string.

Any assistance would be appreciated.

Re: Matrix First Column (Rows) Formatting

wdx223_Daniel — Mon, 07 Nov 2022 07:40:47 GMT

use Table Visual, right click the first column, select condition formatting

Re: Matrix First Column (Rows) Formatting

BasilRudham — Mon, 07 Nov 2022 08:03:06 GMT

Hi wdx223_Daniel, thanks for the response but I cannot use a table. I have to use the matrix due to the design of the data model. I have multiple columns sourced across two data tables. The Rows shell is populated from 1st table using the Income statement text column, the Columns shell is populated by text data from a different source and the Values shell is populated by a single column from the same data table as the statement column. Have tried a Table but it doesn't work for the layout that I need.

PREVIEW

Re: Matrix First Column (Rows) Formatting

wdx223_Daniel — Mon, 07 Nov 2022 08:32:41 GMT

how about adding a index column for the first field in the visual?

then put the index in the first column, and add a new measure to show the real value with MAX

then you can add conditional formatting on the new measure.

Re: Matrix First Column (Rows) Formatting

BasilRudham — Mon, 07 Nov 2022 11:55:02 GMT

Hi wdx223_Daniel, thanks for the 2nd suggestion. I do have a numerical index column that I can insert into the matrix as the first column. I unfortunately have no idea on how to then change the index column values to equal the Income Statement column values and then condition them accordingly. As far as I know you cannot insert a measure into the matrix Rows shell, so I would have to create another column first to get the index column value to equal the Income Statement values? Then create a measure to format this new column? Very confusing for a relative newbie to figure out how to do it.

Re: Matrix First Column (Rows) Formatting

Anonymous — Tue, 08 Nov 2022 06:10:10 GMT

Hi BasilRudham ,

You can create a dimension table as below using Enter Data method, then create relationship between this dimension table and the table which the index column exist in base on index field.

Lastly, put the field [Income Statement] to replace the original index column on the matrix visual. And you can follow the official documentation to apply conditional formatting in your matrix visual.

If the above one not working, could you please provide some sample data in the table 'tbl_Income_01' with Text format and your expected result with whole backend logic and special examples? Is [Income Statement] a fact field or measure? And what the index column look like? Is it also from the table 'tbl_Income_01'? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Matrix First Column (Rows) Formatting

wdx223_Daniel — Tue, 08 Nov 2022 08:04:11 GMT

can this help?

Re: Matrix First Column (Rows) Formatting

BasilRudham — Tue, 08 Nov 2022 08:46:50 GMT

Good Day yingyinr, thanks for the response. I have attached a picture of what I want the end result to look like. I can get the values formatted correctly but not the Rows column. A link to a sample is attached below. I hope this helps. Please also remember that I do not want the backround colour chnaged but the font colour.
https://www.dropbox.com/s/n2kd2epglt0pc1a/Income%20Trial%2020221103.pbix?dl=0

Re: Matrix First Column (Rows) Formatting

BasilRudham — Tue, 08 Nov 2022 08:57:24 GMT

Hi wdx223_Daniel, not sure if it will help. I did try it but got the drilldowns on the matrix but no values displayed. Also, this changes the background colour and I need the font colours to be formatted. In response to another reply above I inserted a picture of what I want (as close as possible) and a link to a sample file with what I have done so far. Perhaps this would provide more details of what I have available and what I am trying to process. One reason as to why I use a matrix instead of a table is that the column headers change every month.