Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Conditional Formatting & Group By

Hey All, Any idea if that is possible OOTB?  Or any way to achieve that? I have a table with IDs that appear a few times. I wold like to paint the row(s) in a table visualisation for each group of...
Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    Anonymous Man, this one does not want to give in!  :smileyhappy:
    I am guessing you have filters from other tables flowing into your visual which I think is causing the problem.  I have updated the measure and as far as I can tell it is working with all scenarios.

    Fomatting Measure = 
VAR CurrentID = SELECTEDVALUE(Table1[ID])
VAR FilteredCount = 
    CALCULATE(
        COUNTROWS ( 
            FILTER ( 
                VALUES(Table1[ID]), 
                Table1[ID] > CurrentID
            )
        ),ALLSELECTED()
    ) + 1
VAR FilterTrap = COUNTROWS(VALUES(Table1[ID]))
RETURN IF ( NOT ISBLANK( FilterTrap ), IF ( ISINSCOPE(Table1[ID]), IF ( ISODD ( FilteredCount ), "#7195BE", "#68CCE4" ) ) )

    Sample file here: https://www.dropbox.com/s/y230n9u0wo08wjp/IDFormatting.pbix?dl=0
    Here is a view with filters applied from both the table itself and from a related date table and it still works.

    A calculated column is not an option because the calc is static and if you filtered out an even row but left the two odd rows on either side the calculated column would not update to change the coloring.  Our measure will since it is a count based on the visable IDs.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi jdbuchanan71 

Yes, strange. Restarted my machine... the ultimate solution. :catwink:

Anyhow, I am getting the same results with the measure.

Different consecutive IDs are getting the same color code.

Table is sorted by IDs.

Thanks!
A

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    Anonymous Man, this one does not want to give in!  :smileyhappy:
    I am guessing you have filters from other tables flowing into your visual which I think is causing the problem.  I have updated the measure and as far as I can tell it is working with all scenarios.

    Fomatting Measure = 
VAR CurrentID = SELECTEDVALUE(Table1[ID])
VAR FilteredCount = 
    CALCULATE(
        COUNTROWS ( 
            FILTER ( 
                VALUES(Table1[ID]), 
                Table1[ID] > CurrentID
            )
        ),ALLSELECTED()
    ) + 1
VAR FilterTrap = COUNTROWS(VALUES(Table1[ID]))
RETURN IF ( NOT ISBLANK( FilterTrap ), IF ( ISINSCOPE(Table1[ID]), IF ( ISODD ( FilteredCount ), "#7195BE", "#68CCE4" ) ) )

    Sample file here: https://www.dropbox.com/s/y230n9u0wo08wjp/IDFormatting.pbix?dl=0
    Here is a view with filters applied from both the table itself and from a related date table and it still works.

    A calculated column is not an option because the calc is static and if you filtered out an even row but left the two odd rows on either side the calculated column would not update to change the coloring.  Our measure will since it is a count based on the visable IDs.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hey jdbuchanan71 

    Thanks a lot!
    The last solution works perfectly.

    Kudos to you :)

    Cheers!
    A

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hey jdbuchanan71 

    Looks good at first sight.

    Will test it a bit before openning the champagne.

    I'll update soon...

    Cheers mate!
    A