cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
skeeder16 Regular Visitor
Regular Visitor

Conditional Format based on Text - NOT COUNT - How to Remove Count

I'm trying to utilize the following method to colorize background of cells based upon the status TEXT that's in them. I found a video that demonstrated this method, but in the video the person was able to select: Conditional Formatting > Background Color it comes up with "Based on field": "Count of MyFieldName"

I don't want the "COUNT" - how do I turn this off so I can simply select MyFieldName as is done in this video link (4:48)?

 

If I can turn off COUNT then I should be able to proceed w/ setting my parameters that will work in conjunction w/ my New Measure (shown in the code window)..

I notice before choosing the "Conditional Formatting" item, there's a list of items and the only thing checkmarked is "Don't Summarize"... (COUNT is NOT checked).. Options available in list are:  Remove Field, Rename, Move, Conditional Formatting, Don't Summarize, First, Last, Count (Distinct), Count, New Quick Measure, Show items w/ no data, New Group.

As mentioned the ONLY thing checked is "Don't Summarize" - I tried unchecking it to see if it would remove Count but it won't uncheck..

Images attached to show what I see..

I don't understand WHY the middle says "Summarization" too, if "Don't Summarize" is obviously already check-marked!?  If that dropdown is clicked there's only 2 options: "Count" and "Count (distinct)"count-not-wanted.giflist-w-count-checked.gif

Video referenced: 

https://www.youtube.com/watch?v=Y8xmJgVjdZs

 

 
Progress Colorization = 
VAR ProgressType = SELECTEDVALUE('Table1 (3)'[Sent Received])
RETURN

SWITCH(TRUE(),
ProgressType = "Complete", 1,
ProgressType = "In-Progress", 2,
ProgressType = "Not Started", 3
)
8 REPLIES 8
JosefPrakljacic Senior Member
Senior Member

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

Hey @skeeder16 ,

 

how about writing a calculated column that represents the color you want for that row/value.

Proper explanation can be found here .

 

In short

  1. Open the conditional formatting pane
  2. Format by Field Value
  3. Choose your calculated column and the summarization First Value
  4. Finish#

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

skeeder16 Regular Visitor
Regular Visitor

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

I've attempted to use the exact format shown in the hyperlink provided however, it's saying the syntax is incorrect/cannot find the field name I'm using (however, it's clearly correct when you look below at the field name).. Looked for a way to click within the [ ] brackets and auto-insert the field to try to resolve but could not find a way to do that - very, very new to PBI any expansion on how to get this working via this method or other - greatly appreciated... @JosefPrakljacic TIA

Note: Originally it was just named the default: Table1 (3) (3rd table) as shown in my orig pics but it seemed to hate the Table Name too, so I renamed the table to "STATUS".. now it hates the Field Name! Smiley Sad

 

ColorDAX = SWITCH(TRUE(),STATUS[Sent Received]="Complete", "#FF7F00", STATUS[Sent Received]="In-Progress", "#2AAAFF", STATUS[Sent Received]="Not Started", "#7F55FF")

field_cannot_find_name.GIF

JosefPrakljacic Senior Member
Senior Member

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

Hi @skeeder16,

 

are you trying to do this in a measure?

 

Try it as a calculated column.

 

WHen you use a measure you are missing the row-context and you there fore need a function that will work as a iterator. In your case I would jsut put the code in a calculated column 😉 

 

BR,

Josef 

skeeder16 Regular Visitor
Regular Visitor

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

@JosefPrakljacic  yes, measure is the only thing I've really toyed w/ as a newbie.

Not sure how I would add a new calculated column yet and retain the same exact look (that client wants to see).. Is it as simple as would be done with Excel? Click on the existing "Sent Received" column, then insert a new one (click "New Column" from ribbon, name orig field "Sent Received1" and name new column "Sent Received".. then a basic formula/function "=Sent Received1" to carry over the contents.. then add the other script within the scripting bar area? Finally hide the orig field called "Sent Received1"? Stab in dark here.. If you could include exact steps to achieve what you're suggesting I'd greatly appreciate it (and learn from it) 😃

JosefPrakljacic Senior Member
Senior Member

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

May I ask you for a Drive/OneDrive/DropBox Link where you have your powerbi file?

 

With sample data it would be easier for me to show you how its done.

 

 

skeeder16 Regular Visitor
Regular Visitor

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

1st wk newbie here, had to go figure out how to create a new workspace to add a test file to it that did not hold private data. (check/done) -- now that I've done that I'm not seeing how to generate/get a link to be able to share that file OR that new test workspace with you... Assume that when a link is generated that it is only to that one file and the other party has no other access to other files correct? Can you tell me how to collect a link to that one file within that new "TEST" workspace to share w/ you? @JosefPrakljacic 

 

Alternatively, would it be easier to adjust the "Measure" method with whatever functions you mentioned? Thx

JosefPrakljacic Senior Member
Senior Member

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

Hello,

I apologize for the late feedback I was in appointments.

 

  1. You have the Power BI Desktop Client.
  2. Here you go on save and save it somewhere where you can find the file afterwards.
  3. You then open an Onedrive or Google Drive or any other fileshare and upload this file.
  4. Then you send me the link to this file.
  5.  

Don't think about workspaces or the like - all I care about is that I get this Power BI file and since you can't upload FIles in this forum, you have to send me a link to your file



Have a nice day!

BR,
Josef

skeeder16 Regular Visitor
Regular Visitor

Re: Conditional Format based on Text - NOT COUNT - How to Remove Count

Thx for clarification @JosefPrakljacic  ; I've provided a link below using the Box site.

I was able to get the colorization to work using 2 diff methods but neither of which are good/or work as desired..

 

Method 1:

Using the TEST001 file (found at the link) I was able to use this to match a column of text against a column of text... IF match, then it colorized as desired. (I mixed hex color refs and wording to see if there were limitations but both worked great.) Problem is, this is not exactly what I need...

My Background Color = 
IF([Sent_Received] = [Recon_Built], "#85ff33",
IF([Sent_Received] = [Recon_Built], "Yellow",
"Red"
))

When you open the TEST001 file/project, you'll find no colors working because I changed the script to more what I NEED to occur.. (obviously it's not the correct syntax or something else is keeping this simplistic version from working).. If you or anyone can help correct the below -- it's what I'd prefer:

My Background Color = 
IF([Recon_Built] = [Complete], "#85ff33",
IF([Recon_Built] = [In-Progress], "Yellow",
"Red"
))

I won't talk about the other method I used in a diff file just yet because it works - but hate that option because it required many, many steps to accomplish.  Thanks in advance, Chris

Link to Test001:

https://app.box.com/s/4dq49w6fznash4gqfk11oa6sx72kj4y6

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors