Conditional Formatting based on field value not working with SQL database

[khampton]

I have been using conditional formatting on the background colour of the Cell Elements area to colour in all cells for a given segment of a matrix. Done in the visualisations pane, not DAX.

The formatting is based on a field in the data which has the colour hex code and it has worked perfectly as shown below:

The issue I'm having is when I use the exact same data but as a direct query from a SQL database instead of pasting into excel, I seem to have the below strange result where if both columns are blank, it does not apply the formatting. If I view the data point of the white cell in a table, it shows the correct colour hex code. This also works fine if one of the Amount or Value Fields is populated and the other is blank but for some reason when both are blank, it shows white.

The raw data is below and as mentioned, works absolutely fine from excel (pasted from the SQL result) or by entering data into power BI. The only time I have the issue is when the data source is the SQL dB. Any ideas?

[Anonymous]

Hi @khampton ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[khampton]

I don't have the option to attache a pbix file but the data is below if you're able to replicate the two scenarios.

I created a test file with one data source pasted directly into 'enter data' and for the other data source I created a dummy database table with the same data below, again the matrix does not colour double null fields when the data source is the database.

Product	Account	Field	Amount	Value	Cell Colour
Product A	ABC Test	1			#FFC107
Product A	ABC Test	2	20000		#FFC107
Product A	ABC Test	3		Text	#FFC107
Product B	ABC Test	1		Text	#FFC107
Product B	ABC Test	2	15000		#FFC107
Product B	ABC Test	3		Text	#FFC107

[Anonymous]

HI @khampton,

I suppose these blank parts are not existed correspond records in your table records, right? If that is the case, you can't use Dax expression to check and locate the specific row/ column combo due to the records not existed.
For this scenario, I'd like to suggest you create two unconnected tables with all row/column field values and use their field on matrix to replace original table fields as group.
After these steps, you can create a measure expression to extract the current category field value and use them as condition to lookup row table records, and setting default result if can't find correspond records on that table.

Using the SELECTEDVALUE function in DAX - SQLBI

Regards,

Xiaoxin Sheng

[khampton]

These fields don't have values but do need to be yellow. There is a default Cell Colour which is white when all 3 fields of a product are blank which also works correctly in the data, therefore I cannot set another default for the blank yellow calls to be yellow. I'm just confused why this works perfectly from an excel spreadsheet of the same data. I am not using DAX currently, I'm using the below conditional formatting:

Both of the above matrices use the exact same data and the exact same conditions, it's just the data source which is different.