Forum Discussion
Data color in table or matrix
Hi, Is it possible to have the table entries (for example rows) in the data color (the one that is also used in the visualization for bars etc).
I don't see how I can fix that with conditional formatting and it should not be static of course.
This example, I'd like to be able to not display a legend below, because the color match the entries in the table (either background, dots, or text color would be fine).
Thanks for your help
8 Replies
swise001Continued Contributor
Currently there's no 'automatic' way to link the colors used in the line chart to those used in the matrix/table.
There's a modified work around you may consider - if you only have a handful of potential 'applications' - as it does involve setting the colors. Consider the following:In this example - you could use a SWITCH statement to declare the color of each 'application':
Row Colors = SWITCH(SELECTEDVALUE('Table'[Item]),"Sample1","#79BEDB","Sample2","#266A2E","Sample3","#6B78B4")
Then set the conditional formatting of each column to "Field Value" - and point it to this formula.This can be done with every column in a Table visual as shown above - but only works with the 'values' columns if you use a Matrix.As for the line chart - there's no way "yet" to programmatically set the colors - or harness the same formula. So you are stuck selecting each application - and setting a custom color using the hex codes - to match what was estabished above.
However, once it's set up - it should persist - even as applications come and go. But it will not account for new applications that aren't included in the list.- DataZoe
Microsoft Employee
SysLostInBI While you can't adjust the legend on a visual, you can try to match that matrix to match it.
I have data by US state names, and when I put that on a line chart, it defaults the legend to be alphabetical and the colors run through default colors.
1. In transform data: I created a dimension table for the states, by duplicating the data table, choosing only the states column, removing duplicates, sorting it alphabetical, then added an Index Column. The goal is to get 0-49 for each of the state names starting with Alabama. It's great to that here so that if say a new state does get added, it will just add it automatically.
2. After applying those changes, I create a relationship to that dimension table on the state name. and change where i used that state name in my visuals to use the new dimension table version.
3. I then created a measure to get me the that index.
Index Measure = min(Category[Index])4. Then I created a conditional format on the matrix conditional formatting for background color (or font color, whatever you want) and you you set it up to be based on that "Index Measure" and simply choose the default colors would be starting with 0:
Just go across the top in the default colors, then 2nd row, etc. Mine also had some greens, so I just went and got it from the data colors that defaulted in on the line chart.The end result is now the matrix will copy the default legend of the line chart. - amitchandak
Super User
SysLostInBI , Conditional formatting can not be done visual using legends
Hi SysLostInBI
I would have loved if Power BI had a extensive functiolality like this.
You can post a Idea for the same.
As of Now, I would manually select the color for each datra in line chart conditional formatting is not an option for this
, and conditionally format the top chart.
Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!
Regards,
Pranit- fhill
Resident Rockstar
This is the closest thing I could find currently available, but I don't it it will help, as when you add Application to the Legend of the Bar Chart, it will override these conditional colors...
https://powerbi.jamesdales.com/2019/02/21/line-charts-with-conditional-formatting/
Hope you find something!
FOrrest
Wow impressive, the amount of feedback in this short time.
I was pretty sure that my lack of experience is the problem - but seems to be a limitation.
All your help is much appreciated, I wonder why nobody mentioned an R script solution.
I'm not experienced enough yet - but do you think it is even possible / or the right solution to try it with an R script?Btw, I'm not ignoring the solutions of using a table with colour or specify them with conditional-formating. I'm going to look into that of course.
- parry2k
SysLostInBI you can surely use R script/visuals but you have to be careful when you publish these reports on Power BI Service and to make sure R libraries you use, supported by Power BI service, otherwise if you have the skill set, you can surely use it. Just be cautious.
HI SysLostInBI,
Perhaps you can try to use the custom theme to setting default data color sets, these charts and visual will auto transformed colors based on color dataset defend if you used legend on your charts.
Use report themes in Power BI Desktop
AFAIK, conditions color formatting not works on the chart with legends. Perhaps you can refer to parry2k's suggestion to use R or python visual to manually plot your graph based on data table field values.
Regards,
Xiaoxin Sheng
