Forum Discussion

khampton's avatar
khampton
Frequent Visitor
2 years ago

Conditional Formatting based on field value not working with SQL database

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 form...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    khampton
    Frequent Visitor
    2 years ago

    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.

     

     

    ProductAccountFieldAmountValueCell Colour
    Product AABC Test1  #FFC107
    Product AABC Test220000 #FFC107
    Product AABC Test3 Text#FFC107
    Product BABC Test1 Text#FFC107
    Product BABC Test215000 #FFC107
    Product BABC Test3 Text#FFC107

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        khampton
        Frequent Visitor
        2 years ago

        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.