How to get conditional formatting for dynamic columns in a matrix

Contoso.pbix (4.7 MB)

My customer is requesting a dashboard containing a matrix that has over 50 columns. They are from an SSRS/Excel background, and whilst you could get away with this in Excel it clearly looks terrible in Power BI as you have to scroll to see more than a few of them.

I am experimenting with dynamically adding columns onto a matrix based on their selection(s) from a slicer. This works reasonably well, and, using the DAX FORMAT function I can make sure financial values, numeric values and percentages are presented correctly.

It’s at this point though the “workaround” nature of this approach seems to become apparent. What I would love to do is:

  1. Dependent on the measure/column name set data bars on/off
  2. Dependent on the measure/column name and its value set the font colour.

I have attached an example I have built using Contoso.

Is what I want to do possible?

Do you have a better idea?

Cheers!

@Jamie ,

Good to see you back on the forum! Unfortunately, I don’t think this is yet possible within Power BI, but I would be thrilled to have some clever forum member prove me wrong.

This issue here is that I think the only way you can access measure names in this way is through calculation groups, and I believe conditional formatting is still not possible within calculation groups. However, I’m loopingin eDNA expert @MudassirAli , who is our go-to expert on calculation groups to see if he has any additional input.

Sorry I don’t have something better for you…

Hi Brian, thanks very much all the same for taking time to respond. No worries.

An alternative for me would be collapsible columns, like you have in Excel, which I could then use to hide various sections. I see that there is a third-party control available that can do this, but knowing the customer, they would push back against that.

Thanks again,

Jamie

Hello @jamie, goos to see you are having progress in solving your problem.

Did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Hi @jamie, we’ve noticed that no response has been received from you since August 22.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @jamie, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Is it possible to only return rows where one or more of the selected measures has a value? In my real-world scenario, I am getting back thousands of blank rows becuase (for example) there are no sales for that product/period. Any help gratefully received!

***Edit.

The main idea behind dynamic addition of measure groups was to enable the users to build their own reports. But having had a look at the use of customising visuals and perspectives, I am of the mind that perspectives are the way forward!