Solution desperately needed for conditional formatting by an algorithm involving other tables.

[Anonymous]

Hi, I am looking to figure out a solution in Power BI that can do conditional formatting to a matrix where each column has its rule to decide how to colour its background. The rules are saved in a table called 'Control File'. My thought is to create a measure for each column in the matrix and the measure can process the relevant rules (rows) in the 'Control File' and return a value of -1(red), 0(no color) or 1(green). Then the measure is used in the conditional formatting window of each column in the matrix. To be noted, some columns in the matrix are fed by measures.

This is the matrix and I just included two columns here for example. There are more to the right. ID represents different portfolios. NET_ESG_RISK_SCORE_P is a column from the table 'BI_GREEN_BATCHING1' and B1_%CARBON_SCOPE1 is a measure calculated on top of some columns in another table.

In order to color NET_ESG_RISK_SCORE_P column, we need to refer to the 'Control File' table as below:

Each row represents a rule of formatting for different columns in the matrix. The first column in 'Control File' is 'Metrics Header' showing which column in the matrix each rule apply to. You can see the first 6 rows are 6 rules to check to work out the background colour of NET_ESG_RISK_SCORE_P in matrix. There is only one rule for the column B1_%CARBON_SCOPE1.

Within each rule, there are a number of values required for the comparison what determings background color, take the first rule as an example. 

It means taking the value of NET_ESG_RISK_SCORE_P for portfolio "VEGA EURO RENDEMENT" on the "Portfolio" level (a filter that has been applied to the column of 'BI_GREEN_BATCHING1'[Output_ID]) to compare with the value of NET_ESG_RISK_SCORE_P in another table called BI_GREEN_BATCHING2 (this name read from 'Control file'[BM Table in PBI]) filtered by [ID] = "GLOBAL_ISR_UNIVERS" (read from 'Control file'[BM name]) and [OUTPUT_ID] = "Reduced Universe" (read from 'Control file'[BM classification level]). If it is greater than that value, we need to check in the column of 'Control file'[HL] of that row, if the value equals "H", means the higher the better, then colour the cell in green, otherwise red. if the value equals "L", means the lower the better, then the green and red should swap. 

For some columns in the matrix, the rule is very simple, just need to compare with a fixed number. For example, [B1_%CARBON_SCOPE1] only compares with 0.9, if it is over 0.9, then red given 'Control file'[HL] = 'L', otherwise green. If 'Control file'[HL] = 'H', swap the colour. 

The rules in 'Control file' don't really include every column in the matrix. For those not showing in 'Control file', there is no need to colour them. 

As I mentioned at the beginning, I want to create a measure for each column in the matrix and the measure can process the relevant rules (rows) in the 'Control File' and return a value of -1(red), 0(no color) or 1(green). Then the measure is used in the conditional formatting window of each column in the matrix. Now I am struggling with creating such measure. There are two difficulties I can see:

1. How to refer to a table by string read from 'Control File'[BM Table in PBI]? Is there a DAX function like INDIRECT in Excel?

2. How to retrieve values of BM classification and BM name which are then used in filters in the BM table to get the BM value which are then compared with value in the column of matrix?

If you have got better ideas of implementing such a pain, please also let me know. 

The matrix is mains fed be the table 'BI_GREEN_BATCHING1'

[lbendlin]

You do the pointer math with DAX variables inside your measures. In M you can use row and column references  {...} and [...]

[lbendlin]

You can use any field value as the basis

[Anonymous]

Thanks, I will learn it and try. Also, do you know if there is a DAX/M function like INDIRECT in Excel to refer to a table by string?

[lbendlin]

Imagine for a second having to maintain that process, or to document it for your successor.

Anyway - are the color values dependent on user filter interaction or could they be expressed in a calculated column or Power Query column?

[Anonymous]

The thing is conditional formatting in Power BI table only take the value of a measure to construct rules. If you use a column, you would have to do some operation to turn a column into a value, e.g. sum, average etc.