Highlight fist occurrence (conditional format)

1

I will highlight only the fist occurrence but I have no idea how to check this. The formula looks like that, but it highlights all columns with the same value. So it needs some limiting to 1 (or first/last column. Maybe something with count() or First() / Last()?

thisTable.Filter(Jahr= SelectYearSport).[Km/h].Max() =thisRow.[Km/h]

Does anyone have any ideas?

2

Hi @Frank_Arnold ,
the formula does seem to be correct.

It might be a data type issue, perhaps: could you share your doc (or a sample of it) to better ding into it?

Thanks!

3

Maybe it’s easier to describe it in a more general way… if the formula finds a match on multiple rows, the formatting is also applied to all matching cells (so e.g. multiple rows are formatted) How to change a formula so that in case of multiple matches only the first/last entry in this result list is used for formatting?
There are some functions like “Last()” or “First()” but also the function “Count()” which you can certainly use here. But how this is solvable in the formula for formatting I don’t know.

4

Hi @Frank_Arnold,
you might obtain something like this with Rank()
The order of ranking depends on your sorting logic, but this should work.

Something like:

If(thisTable.Filter(Jahr= SelectYearSport).[Km/h].Max() = thisRow.[Km/h], 
  thisTable.Rank(thisRow)=1, 
  false)

Let me know if this goes to the right direction.

Cheers!

5

It goes in the right direction. But the part with “thisTable.Rank(thisRow)” it seems that “thisRow” doesn’t have the list from the previous Filter, so it always returns false because “thisRow” points to an empty list?

6

Ho @Frank_Arnold,
this is the reason why having a concrete example would help :slight_smile:

Anyway, you are right: we need to narrow the dataset to be effective with ranking I think this should work (do let me know otherwise: I haven’t built up an example myself):

If(thisTable.Filter(Jahr= SelectYearSport).[Km/h].Max() = thisRow.[Km/h], 
  thisTable.Filter([Km/h] = thisRow.[Km/h]).Rank(thisRow)=1, 
  false)

Cheers!

7

Great, this second “filter” does the trick. Thank you!

By the way… “thisTable” does it always refer to the currently displayed range of the table or does it refer to the complete table (i.e. also the records that are currently not displayed because they are filtered)?

8

Yep: it’s always the full table.
However, you can put the filtered dataset in a variable (through WithName()) and refer to the same filtered result anytime.

In your case:

thisTable.Filter(Jahr= SelectYearSport && [Km/h] = thisRow.[Km/h])
  .withName(yearFilter,
    If(yearFilter.[Km/h].Max() = thisRow.[Km/h],
      yearFilter.Rank(thisRow)=1, 
      false)
)

(Please, double check for possible syntax errors: haven’t tested).

9

“withName” I did not know yet, a very good tip!

1 Like