Highlight specific rows in a table visual that meets a condition

[sohananahid]

Hi all: Good day! I have a ‘Summary table’ visual that shows maintenance notice data for different pipelines. It shows pipeline name, event subject, start date, end date, update date, etc. as below

I want to highlight those rows that have an update date from yesterday [1 day earlier than today] and beyond. I have created a query,  prevDayFilter

I am trying to use updateDate>=prevDayFilter to apply to specific rows that will meet this condition and get it highlighted with a color, say yellow.I have created another query, RowHighlight as

= IF (SELECTEDVALUE(genscape_maintenance_events[updateDate])>=prevDayFilter; 1;0))

As I start applying conditional formatting for each Column in the ‘Table’ visual to have specific rows get highlighted: I get an issue as below: the Based on field shows ‘Count of RowHighlight’ instead of only ‘RowHighlight’ – so it highlights all cells of that column, e.g. PipelineName in yellow rather than only those cells that meet the condition!

Any suggestion? Many thanks in advance. 🙂

I followed a post: https://community.powerbi.com/t5/Desktop/highlight-specific-rows-in-matrix/m-p/893600

[tex628 · Accepted solution]

Try wrapping the prevdayfilter column in a selectedvalue(). Even if there is just 1 value and 1 column in a table you still cant refer directly to the table, instead you need to do an aggregation on the column to get the appropriate value. 

highlightRow = 
VAR Filter = SELECTEDVALUE( prevDayFilter[Column] )
Return
IF (SELECTEDVALUE(genscape_maintenance_events[updateDate]) >= Filter, 1,0)

/ J

[v-diye-msft]

Hi @sohananahid 

You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

[tex628]

Hey @sohananahid!

I think you're running an old version of Power BI, notice the differences in layout here:

My client:


Yours:

I think this is the casue of the issue as I can't really see anything wrong with what you've been doing. 

Let me know how it goes. 

/ J

[sohananahid]

hi @tex628: I got Power BI Desktop Version: 2.78.5740.721in June 2020. Will that make this much difference!? 

[tex628]

I don't know 🙂 But lets make a reverse test.

I've attached a sample file with the formatting applied, try opening it and check that the formatting is similar to what we're aiming for.

Then try removing and reapplying the conditional formatting. It should give us an idea if the issue lies with the client or the measure itself. 

/ J

[sohananahid]

Hi @tex628   : Thanks for sending the file. I opened it in my desktop env. I have February edition as mentioned earlier. It worked in my env. I am trying to follow it for my issue.

I have a query, prevDayFilter as below:

= "'" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -1),"yyyy-MM-dd") & "'"

As I try to create the measure

highlightRow = IF (SELECTEDVALUE(genscape_maintenance_events[updateDate]) >= prevDayFilter, 1,0)

to use in the Rules for the background color for the rows

I get error: "DAX comparison op do not supprot comparing valies of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

I tried few things, not working! Any help will be highly appreciated! 🙂

[sohananahid]

Hi @tex628 : I tried to simplify the first query, prevDayFilter as

= Date.AddDays(Date.From(DateTime.LocalNow()), -1)

so that it's a date like 9/3/2020, but I get issue with the measure now:

"The expession return multiple columns. Multiple coulumns can not be converted to a scala r value."

This was an attempt, but didn't work! 😞

[tex628]

Did you add  prevDayFilter as a 1 row table? 

/ J

[sohananahid]

Hi @tex628: I created 'prevDayFilter' as a blank query in Power Query, then renamed it. I created the measure on the 'maintenance summary table' data source and use the prevDayFilter query! The measure is supposed to check ' updateDate' on each row in the 'maintenance summary table' to decide whether to highlight the row, etc. Not sure what to do! 😞 

[tex628 · Accepted solution]

Try wrapping the prevdayfilter column in a selectedvalue(). Even if there is just 1 value and 1 column in a table you still cant refer directly to the table, instead you need to do an aggregation on the column to get the appropriate value. 

highlightRow = 
VAR Filter = SELECTEDVALUE( prevDayFilter[Column] )
Return
IF (SELECTEDVALUE(genscape_maintenance_events[updateDate]) >= Filter, 1,0)

/ J

[sohananahid]

Hi @tex628: Thanks a lot for your continued support in getting to this solution! I had to tweak a little to update the measure:

highlightRow = VAR  x= SELECTEDVALUE(prevDayFilter[prevDayFilter])

Return

IF( SELECTEDVALUE(genscape_maintenance_events[updateDate]) >= x, 1 , 0 )

Best, Sohana 🙂

[sohananahid]

Hi @tex628 & all: Good day! There is an issue I found later. The prevDayFilter as

= Date.AddDays(Date.From(DateTime.LocalNow()), -1)

shows right date as 9/15/2020 [today is 9/16/2020]. As I started to do the data 'Refresh', first 2 days it works fine- highlights rows with update date as today and yesterday. Then from 3rd day on it highlights earlier days rows too! Like today as I did the data refresh, it highlighted rows with update date of today [9/16/2020], yesterday [9/15/2020] and also 9/14/2020. 

The measaure is:

highlightRow = VAR  x= SELECTEDVALUE(prevDayFilter[prevDayFilter])

Return

IF( SELECTEDVALUE(genscape_maintenance_events[updateDate]) >= x, 1 , 0 )

Not sure why it's highlighting rows with 'updateDate' earlier than previous day [yesterday]! Any help is highly appreciated!

[sohananahid]

Hi all, @tex628 : I have 1 more question on this thread.

 The table visual I have by default gets sorted with the first Column, pipeline Name [alphabetically shows names with A, then B, …].  Since I have the rows getting highlighted with values of Update Date, I would like to sort the table with this column, 'Update Date' so that every time the table gets refreshed, the most recent data [based on Update date] shows up first, then next most recent, etc. Also then the users don't have to scroll up and down to see which rows got highlighted with green, they show up on the top.

Do you have any suggestion how to do [default set up for sorting the table based on a preferred column] that? Many thanks.

[tex628 · Accepted solution]

Just choose select the column you want the table sorted by, then apply either asc or desc sorting:

Whatever settings you have applied when you publish to Power BI Service will be the default for the users when they access the report. 

/ J

[sohananahid]

Hi @tex628 Thanks a lot for your support! 🙂 

[lbendlin]

Change the summarization type,  or change your measure into a boolean.

[sohananahid]

Hi @lbendlin: Thanks for your response. How do I change the Summarization type? Here is what I get for selecting the rule:

[sohananahid]

Hi all: Good day. As I tried to make the second query, RowHighlight as having boolean value-  1 [or 0] as below

= IF (SELECTEDVALUE(genscape_maintenance_events[updateDate])>=prevDayFilter, 1,0))

i find same issue as stated earlier [Format by  Rules]. Any help will be highly appreciated.