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
jayeckles
Advocate III
Advocate III

Conditional formatting for non-summarized item

Data source is a live-connected SSAS Tabular Model.

I have a numeric field in a table, and I'd like to conditionally format the values for that field in that table.  I have two issues:

 

1. If I have the value set to not summarize, I can't access a conditional formatting menu.  

2. The field is a GPA field.  I only want to highlight values <=1.5.  I can't figure out how to do anything other than change colors along the entire spectrum of values for the field.

7 REPLIES 7
th3h0bb5
Resolver II
Resolver II

Can you attach a picture of the visualization you're wanting?

 

If you're doing something like a table, just break it down to the highest level of detail. That way, each GPA value will have it's own line. You don't need to click 'don't summarize' that point and can apply conditional formatting. In the example I pasted below, each Month has it's own row in the data, so I can conditionally format the purchase values.

Untitled picture.png

Here's a screenshot.  trmgpa is the field in question.  As you can see, if it's set to "Don't summarize", there is no "Conditional Formatting" option.  If I choose any summarization, say Average for example, then the "Conditional Formatting" option becomes available.

 

 

pbiexample.png

Given this visual, I don't see why you would need to click 'Don't Summarize'. Set the [trmgpa] calculation to average or sum and then apply the conditional formatting. Is there a reason that didn't work for you?

I have no desire to summarize on the field.  For one, I have records that have no value (null) for that field.  For another, none of the summarizations are particularly useful in this context.  I just want the value to be red if < 1.5.  

Gotcha. Provided that this is the lowest level of detail though, your aggregate will be identical to your value.

 

For example, take the top row of your table:

asdf.png

 

If this represents a single row in your data source (let's say this is Bill's row), then Bill's GPA is 3.16. 3.16 is also the average of Bill's GPA and the sum of his GPA (since there is no other data taken into the calculation except for Bill's on this row).

 

Which takes me back to my initial remark; if you are at the lowest level of detail (Bill gets his own row and is not grouped in with anyone else), then you don't need to click 'not summarized' and can use conditional formatting.

As I said before:

   For one, I have records that have no value (null) for that field.

If I use a summarization, those records get dropped from the table.  Not OK.

 

Not to mention, "Do Not Summarize" is the default.  It's not like I'm starting with a summarization and actively turning it off...

 

 

Sorry, I should have addressed that as well. There is a kind of odd work-around that will let you display null values in a table.

 

  1. Create a bar graph and pull in your GPA and one other field (it doesn't really matter which.)
  2. Click the axis and select 'Show Items with no data'
  3. Convert that bar graph back to a table and add in the rest of your rows. This new table will now display null values as their own row.

Here's the table before this process (with madeup data):

 

before.png

 

And here's the table created using the process above:

 

after.png

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.