cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Highlight specific rows in a table visual that meets a condition

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

sohananahid_0-1599081859540.png

 

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

sohananahid_0-1599082053391.png

 

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!

 

sohananahid_1-1599081859545.png

Any suggestion? Many thanks in advance. 🙂

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Highlight specific rows in a table visual that meets a condition

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


Connect on LinkedIn

View solution in original post

Highlighted
Super User II
Super User II

Re: Highlight specific rows in a table visual that meets a condition

Just choose select the column you want the table sorted by, then apply either asc or desc sorting:
Gif.gif
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


Connect on LinkedIn

View solution in original post

17 REPLIES 17
Highlighted
Super User II
Super User II

Re: Highlight specific rows in a table visual that meets a condition

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

Highlighted
Post Patron
Post Patron

Re: Highlight specific rows in a table visual that meets a condition

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

 

sohananahid_0-1599088348677.png

 

Highlighted
Super User II
Super User II

Re: Highlight specific rows in a table visual that meets a condition

Hey @sohananahid!

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

My client:
image.png

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


Connect on LinkedIn
Highlighted
Post Patron
Post Patron

Re: Highlight specific rows in a table visual that meets a condition

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

Highlighted
Post Patron
Post Patron

Re: Highlight specific rows in a table visual that meets a condition

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. 

Highlighted
Community Support
Community Support

Re: Highlight specific rows in a table visual that meets a condition

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.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
Highlighted
Super User II
Super User II

Re: Highlight specific rows in a table visual that meets a condition

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


Connect on LinkedIn
Highlighted
Post Patron
Post Patron

Re: Highlight specific rows in a table visual that meets a condition

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! 🙂

 

 

 

 

Highlighted
Post Patron
Post Patron

Re: Highlight specific rows in a table visual that meets a condition

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! 😞

 

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors