cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Reading Value in Table When Showing Rows with Blanks

I have to do some custom coloring that cannot and trying to use conditional formatting, but I am not getting the results i was hoping for.  I have tried many methods, but continue to get the same result.  I have finally fingured out the issue, but I cannot figure out a solution.

What I have done now is create a measure to map values in my data to a number and then base the conditional formatting on that.  However, I now see that some of my values are not being assigned a number.  

image.png

I have the table show rows when the value is 0 and this looks to be the source of my issue.  I have to slicers, that based on the selection, it is only these items that have a value based on the resultant filter are the ones being assigned a number.  

What I want to do is have all values be assigned a number, so that I can do my formatting.  I tried filtering within my measure, but I coudn;t get the syntax correct, as I never got the proper values.  

Here is my measure

color = MAXX(Defects,SWITCH(true,
    Defects[Status]="01-New",1,
    Defects[Status]="02-Reopen",2,
    Defects[Status]="03-Revisit",1,
    Defects[Status]="04-Open",2,
    Defects[Status]="05-Fixed",3,
    Defects[Status]="08-Return",4,
    Defects[Status]="07-Retest",4,
    Defects[Status]="06-Repair",4,1
))
4 REPLIES 4
Highlighted
Resolver I
Resolver I

Re: Reading Value in Table When Showing Rows with Blanks

Hi,

 

Instead of using Measure, what if you create a column in the table using switch logic. For Example

 

ColorCodes = SWITCH(TRUE(),
Table[Status] = "01-New", "#000000",
Table[Status] = "02-ReOpen", "#6DB6FF",
Table[Status] = "03-Revisit", "#FF0000",
Table[Status] = "04-Open", "#008000",
Table[Status] = "05-Reset", "#FFFF00",
Table[Status]="06-Return","#480091"
)
 
Then use conditional formating on Status column by chosing option "Field Value" in Format by, "First ColorCodes" in Based on field and "First" in Summarization.
Highlighted
Helper III
Helper III

Re: Reading Value in Table When Showing Rows with Blanks

Thanks for the suggestion @ORBI, however, that gave me the same result. I was only using the measure as a last attempt, as I tried a similar calculated column, but just did the conditional formatting based on a rule, rather than a value.  I had originaly tried a seperate data table with the statuses and color as the two columns, when I first started to have this issue.  It was only when I tried the measure that I was able to see why my conditional formatting was not working.

Highlighted
Community Support
Community Support

Re: Reading Value in Table When Showing Rows with Blanks

Hi @MTOnet ,

 

You could use ALL() function to ignore the filter. 

However, I still don't kown the reason of blank. Could you please share your sample data and excepted result to me if you don't have any Confidential Information? Please upload your files to One Drive and share the link here.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Highlighted
Helper III
Helper III

Re: Reading Value in Table When Showing Rows with Blanks

Hi @v-eachen-msft , unfortunately, I am unable to share my dataset.  The client has limited sharing on onedrive to those within the corporate network.

 

I did try to use the all() function, but when I did so, all statuses (including those that had been previously been blank) gave a color result of 4.  Here is what I tried.  Let me know if you think it should be done differently

Testcolor =  maxx(all(Defects),SWITCH(true,
    Defects[Status]="01-New",1,
    Defects[Status]="02-Reopen",2,
    Defects[Status]="03-Revisit",1,
    Defects[Status]="04-Open",2,
    Defects[Status]="05-Fixed",3,
    Defects[Status]="08-Return",4,
    Defects[Status]="07-Retest",4,
    Defects[Status]="06-Repair",4,1
))

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Top Solution Authors