Conditional Formatting across 50+ columns

[wonka1234]

hi,

I have 50 columns that I want to apply conditional formatting to. Is there a faster way to apply to all columns instead of me going into each one to change it?

Thanks.

[KNP]

Hi @wonka1234 ,

You could probably do it in code if you have the option to save the file in .PBIP format.

It would work best if the new PBIR preview feature is turned on but be careful, that format comes with some limitatons currently.

Here's some sample code from a report.json file WITHOUT PBIR turned on, in the PBIP file.

        {
          "config": "{\"name\":\"de3d751817c235bd6ee0\",\"layouts\":
                      [{\"id\":0,\"position\":{\"x\":24.628185127405096,\"y\":236.2974518980759,\"z\":1000,\"width\":443.3073322932917,\"height\":233.6349453978159}}],\"singleVisual\":
                        {\"visualType\":\"tableEx\",\"projections\":{\"Values\":[{\"queryRef\":\"_product.CategoryName\"},{\"queryRef\":\"_measures.Sales Row Count\"}]},\"prototypeQuery\":
                          {\"Version\":2,\"From\":[{\"Name\":\"_\",\"Entity\":\"_Product\",\"Type\":0},{\"Name\":\"_1\",\"Entity\":\"_measures\",\"Type\":0}],\"Select\":[{\"Column\":
                            {\"Expression\":{\"SourceRef\":{\"Source\":\"_\"}},\"Property\":\"CategoryName\"},\"Name\":\"_product.CategoryName\",\"NativeReferenceName\":\"CategoryName\"},
                              {\"Measure\":{\"Expression\":{\"SourceRef\":{\"Source\":\"_1\"}},\"Property\":\"Sales Row Count\"},\"Name\":\"_measures.Sales Row Count\",\"NativeReferenceName\":\"Sales Row Count\"}]},
                                \"drillFilterOtherVisuals\":true,\"objects\":{\"columnFormatting\":[{\"properties\":{\"fontColor\":{\"solid\":{\"color\":{\"expr\":{\"ThemeDataColor\":{\"ColorId\":2,\"Percent\":0}}}}}},
                                  \"selector\":{\"metadata\":\"_product.CategoryName\"}}],\"values\":[{\"properties\":{\"backColor\":{\"solid\":{\"color\":{\"expr\":{\"Conditional\":{\"Cases\":[{\"Condition\":
                                    {\"Comparison\":{\"ComparisonKind\":2,\"Left\":{\"Measure\":{\"Expression\":{\"SourceRef\":{\"Entity\":\"_measures\"}},\"Property\":\"Sales Row Count\"}},\"Right\":{\"Literal\":
                                      {\"Value\":\"4000000D\"}}}},\"Value\":{\"Literal\":{\"Value\":\"'#D9B300'\"}}}]}}}}}},\"selector\":{\"data\":[{\"dataViewWildcard\":{\"matchingOption\":1}}],\"metadata\":\"_product.CategoryName\"}}]}}}",
          "filters": "[]",
          "height": 233.63,
          "width": 443.31,
          "x": 24.63,
          "y": 236.30,
          "z": 1000.00
        },

You'll see values of 4000000, #D9B30 and _product.CategoryName near the end. This is referring to my conditional formatting. If you could understand the structure, you could probably add to it, at your own risk.

Now if you were able to use the new PBIR format, it would be much easier to work with. (I'll say it again, it comes with limitations and there's no converting the file back, so make sure you understand them.)

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/visualContainer/1.3.0/schema.json",
  "name": "de3d751817c235bd6ee0",
  "position": {
    "x": 24.628185127405096,
    "y": 236.29745189807591,
    "z": 1000,
    "height": 233.6349453978159,
    "width": 443.30733229329172
  },
  "visual": {
    "visualType": "tableEx",
    "query": {
      "queryState": {
        "Values": {
          "projections": [
            {
              "field": {
                "Column": {
                  "Expression": {
                    "SourceRef": {
                      "Entity": "_Product"
                    }
                  },
                  "Property": "CategoryName"
                }
              },
              "queryRef": "_product.CategoryName",
              "nativeQueryRef": "CategoryName"
            },
            {
              "field": {
                "Measure": {
                  "Expression": {
                    "SourceRef": {
                      "Entity": "_measures"
                    }
                  },
                  "Property": "Sales Row Count"
                }
              },
              "queryRef": "_measures.Sales Row Count",
              "nativeQueryRef": "Sales Row Count"
            }
          ]
        }
      }
    },
    "objects": {
      "columnFormatting": [
        {
          "properties": {
            "fontColor": {
              "solid": {
                "color": {
                  "expr": {
                    "ThemeDataColor": {
                      "ColorId": 2,
                      "Percent": 0
                    }
                  }
                }
              }
            }
          },
          "selector": {
            "metadata": "_product.CategoryName"
          }
        }
      ],
      "values": [
        {
          "properties": {
            "backColor": {
              "solid": {
                "color": {
                  "expr": {
                    "Conditional": {
                      "Cases": [
                        {
                          "Condition": {
                            "Comparison": {
                              "ComparisonKind": 2,
                              "Left": {
                                "Measure": {
                                  "Expression": {
                                    "SourceRef": {
                                      "Entity": "_measures"
                                    }
                                  },
                                  "Property": "Sales Row Count"
                                }
                              },
                              "Right": {
                                "Literal": {
                                  "Value": "4000000D"
                                }
                              }
                            }
                          },
                          "Value": {
                            "Literal": {
                              "Value": "'#D9B300'"
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            }
          },
          "selector": {
            "data": [
              {
                "dataViewWildcard": {
                  "matchingOption": 1
                }
              }
            ],
            "metadata": "_product.CategoryName"
          }
        }
      ]
    },
    "drillFilterOtherVisuals": true
  }
}

Once you understood the format, I suspect it would be very easy to edit in bulk.

Hope this was useful info even if you don't use it.

[audreygerred]

There is not currently a way to conditionally format columns in bulk, they have to be done manually.