cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

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

2019-07-11 09_14_11-Window.png

Cheers!
A

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Conditional Formatting & Group By

@AClerk Man, this one does not want to give in!  Smiley Happy
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.

idformats.jpg

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.

View solution in original post

13 REPLIES 13
Highlighted
Super User III
Super User III

Re: Conditional Formatting & Group By

Hi @AClerk ,

 

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:

 

Condititionla.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Super User IV
Super User IV

Re: Conditional Formatting & Group By

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?

Highlighted
Solution Sage
Solution Sage

Re: Conditional Formatting & Group By

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

Highlighted
Super User IV
Super User IV

Re: Conditional Formatting & Group By

Hello @AClerk 

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 )

 

formatids.jpg

Highlighted
Solution Sage
Solution Sage

Re: Conditional Formatting & Group By

@jdbuchanan71 
EDIT:
Sorry, not working well as I thought.

Maybe I am missing something?

For example (Table sorted by ID)
2019-07-11 10_35_05-Window.png

 

 

 

Spoiler

@jdbuchanan71 

Amazing!
Can you please explain the logic behind the DAX?

Reading it again and again for the last 10 minutes, and not sure I understand it completely.

Understood the basic logic of counting the rows until current row, but want a more technical explanation, if possible.
Thanks a lot,
A

 

Highlighted
Super User IV
Super User IV

Re: Conditional Formatting & Group By

COUNTROWS works over a table.

FILTER returns a table
Our filter returns a table of ALL ID's that are > the current ID, then we count that.  We add 1 because if we don't, on the highest ID it returns a blank.

We then turn the count into a 1 / 2 based on even or odd.

If you want to see the steps working you can change the Return statement in the formula.

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

In the code above I commented out the switch and returned the IDCount variable instead.

Highlighted
Super User IV
Super User IV

Re: Conditional Formatting & Group By

@AClerk , lets try something a bit more dynamic.

Add a measure to your model that we will use to apply the formatting.

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

The colors we are using are listed in the measure, then we apply the formatting based on the value of the measure:

formatidmeasure.jpg

 

Highlighted
Solution Sage
Solution Sage

Re: Conditional Formatting & Group By

Hey @jdbuchanan71 

Not sure how you are doing it. I am not able to format according to a measure. Only a column.

So I am back to square 1 again.

I tried to put your DAX in a column, but it returns empty.

2019-07-11 11_20_51-Window.png

 

Cheers,
A

Highlighted
Super User IV
Super User IV

Re: Conditional Formatting & Group By

@AClerk , That is very strange.  I have uploaded a copy of my testing file here.  Does the formatting work in this file for you?

https://www.dropbox.com/s/y230n9u0wo08wjp/IDFormatting.pbix?dl=0

Perhaps you need to update your PowerBI desktop app?

The file also includes the formatting calc as a column in the table if you need it.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors