Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MTOnet
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
v-eachen-msft
Community Support
Community Support

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.

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
))
Anonymous
Not applicable

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.

Thanks for the suggestion @Anonymous, 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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.