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 IDs. Just for the view be more user friendly.
For example

Cheers!
A

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.

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Anonymous ,

     

    Being a table visual and assuming that you have the ID column as you present it on the image do the following:

     

    • Create the following measure:
    ID CHECK = IF( ISEVEN(SELECTEDVALUE(Table1[ID])) = TRUE();1;0)

    Now do the condittional formatting on your columns based on this measure:

     

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hey MFelix jdbuchanan71 

      Thanks for the replies.

      The ID was just to illustrate the data.

      The IDs, real look like random strings. For example:

      5f239534-5011-6j6b-e9bb-5b2686c1fa61
      ea470a28-a413-d5j0-a6f4-5b26e67789ab

      etc.

       

      Thanks
      A

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        7 years ago

        Hello Anonymous 

        You could add a column to your table that has the ID which would count the number of ID's that are > the current ID then use that count as an odd / even switch to base the formatting on.

        FormattingColumn = 
VAR CurrentID = Table1[ID]
VAR IDCount = 
COUNTROWS(
    FILTER (
        ALL ( Table1[ID] ), Table1[ID] > CurrentID) ) + 1
Return IF( ISODD ( IDCount ) , 1, 2 )

         

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    You could write a measure to look at the ID value and format one way for odd and another way for even based on that measure using conditional formatting.

    TestMeasure = 
VAR FormatBaseOn = SELECTEDVALUE ( 'Table'[ID] )
RETURN 
IF ( ISODD ( FormatBaseOn ),1 ,2 )

    Are your ID's consistent enough for that to work?