Losing conditional formating after using "Show items with no data"

Question

Hey guys,&nbsp;I am using conditional formating on a field to change the background color, in a table.It works great.&nbsp;The problem is that I need to include a column in the table that might or not have values for that relationship so I need to tick the "Show items with no data" option.&nbsp;The problem is that for the records with no data on the new table the conditional formating disapears...&nbsp;without new column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;col1col2col3col4col5col6col7&nbsp;A34ongoing&nbsp;ABCDEFGHI&nbsp;A34ongoingRCabcdefghi&nbsp;A34ongoingRVABC1DEF1GHI1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;including new column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;col1col2col3col4col5col6col7col_from_newtableA34ongoing&nbsp;ABCDEFGHI&nbsp;A34ongoingRCabcdefghiXXA34ongoingRVABC1DEF1GHI1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;moving the column up (notice that all the columns after that column will not have values anymore…)col1col2col3col4col_from_newtablecol5col6col7A34ongoing&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A34ongoingRCXXabcdefghiA34ongoingRV&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Can anyone help me with this?Thanks

tex628 · Answer

Hi&nbsp;Anonymous ,This is an issue i've battled with myself several times. To my knowledge there is no way to do this yet. Where there is no data there is no evaluation being made which furthermore means that there is nothing to format.&nbsp;If I'm wrong and there is a solution I would be very interested in seeing it as well! 🙂Br,J

amitchandak · Answer

Anonymous , I doubt any solution as blank means no value and if add +0 to the measure and try to color that, it will give all the values from dimensions in use so unnecessary values will come

anonymous · Answer

Hi&nbsp;tex628&nbsp;,&nbsp;but the conditional rule does not deppend on the new column. It just depends on the column I calculated with the color (col4).&nbsp;Also, the fact that every time I slide the new column along the table, all the values after that will disapear...what is the explanation for this?

v-lionel-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Could you show the conditional rule formula you created?
&nbsp;
Best regards,Lionel Chen
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

anonymous · Answer

Hi&nbsp;v-lionel-msft&nbsp;,&nbsp;the conditional formating is as follows:Backgroud ColorFormat by Field ValueBased on field: ColorSummarization: First&nbsp;The formula for the field Color is:Color = switch(true(), firstnonblank(TABLE_1[requeststatus_value_id],"NA") = 1, "#0800",firstnonblank(TABLE_1[requeststatus_value_id],"NA") = 2, "#ECB548",firstnonblank(TABLE_1[requeststatus_value_id],"NA") = 3, "#FF0011",firstnonblank(TABLE_1[requeststatus_value_id],"NA") = 4, "#6797E3",firstnonblank(TABLE_1[requeststatus_value_id],"NA") = 5, "#C0C0C0",firstnonblank(TABLE_1[requeststatus_value_id],"NA") = 6, "#000000", "#456789" )&nbsp;TABLE_1 is the table for where I retrieve most of the columns&nbsp; and everything works fine (including the formatting) and only when I include a column from TABLE_2 the problem appears.Not all records on TABLE_1 have a correspondence in TABLE_2, this is why I had to use "Show items with no data".&nbsp;(I am sorry but I do not know how to insert images here).&nbsp;Thanks

Forum Discussion

Losing conditional formating after using "Show items with no data"

5 Replies

Related content

Showing "Data" pane instead of "Fields" pane

Date data showing as "null" despite source data having values

"show items with no data" breaks the visual

Power BI Service refresh shows "Completed" but report displays stale data on-premises SQL Server

Missing "Show data point as a table" in Power BI Service for a map visual

without new column
col1	col2	col3	col4	col5	col6	col7
A	34	ongoing		ABC	DEF	GHI
A	34	ongoing	RC	abc	def	ghi
A	34	ongoing	RV	ABC1	DEF1	GHI1

including new column
col1	col2	col3	col4	col5	col6	col7	col_from_newtable
A	34	ongoing		ABC	DEF	GHI
A	34	ongoing	RC	abc	def	ghi	XX
A	34	ongoing	RV	ABC1	DEF1	GHI1

moving the column up (notice that all the columns after that column will not have values anymore…)
col1	col2	col3	col4	col_from_newtable	col5	col6	col7
A	34	ongoing
A	34	ongoing	RC	XX	abc	def	ghi
A	34	ongoing	RV