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
ihayes916
Frequent Visitor

Convert Column to Measure...

Greetings - 

I am trying to replace an excel sheet w/ "conditional formating" w/ PowerBI...

I know that you can only do "conditional formatting" in PowerBi w/ columns that are 'measures' - but in my scenario, the values in my data are already calculated, I just need to add the visual formating....

Example -

In excel, i have this...

ExcelCapture.JPG

 

But in PowerBi, I cannot achive becasue 'score' is not a measure...

PowerBiCapture.JPG

 

 

 

So, my thought was to create a new 'measure column' so that I can conditional format it....

Something like 'NewMeasureColumn = 'myTable'[score] * 1'  - that way the value never changes....but I cannot figure this out....

Is something like this possible?

Has anyone figured out a simple way to convert a value into a measure so you can apply conditional formating?

 

Thanks!

8 REPLIES 8
v-jiascu-msft
Employee
Employee

Hi @ihayes916,

 

 

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

 

Best Regards!
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MFelix
Super User
Super User

Hi @ihayes916,

You can apply conditional formatting to values from columns or measure the values have to be numbers but it applicable to both.

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



@MFelix - thank you for your reply.

 

Unless I am missing something painfully obvious, you can only apply conditional formating to 'Measures' or 'Aggregated Values', right?

I have column, 'score', that is a number...but when I select that column...I cannot apply any conditional formating...

cap1.jpg

 

But...if i do some type of aggregate function (SUM) then I can apply conditional formating...but this messes up my numbers...

 

cap2.JPG

 

Thoughts?

Hi @ihayes916,

 

In your explanation I didn't understood that your data was in a non aggregated way, if you use a measure since it's based on context you also need to have a aggregator. Is your data one to one? Meaning that your ID column in the table correspond only to one value? you could aggregate the values with a max or a average and then apply the conditional formatting.

 

Without any further information about your data is not easy to give you a solution of a measure, if you can give some sample data and the expected result I cna give you a better answer.

 

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



@MFelix - again, thanks and apologizies for the lack of calrity...

 

The data that I am dealing w/ is flat, non-relational data w/ no ID column.  😞

As it stands today, someone runs a SQL query and exports the results as a flat CSV. The SQL job does all the calculations so there is very little for Power BI to do.  They then take that CSV and append the data to an existing excel workbook that has a bunch of formulas in it....

 

The end result (in excel) looks something like this... (this is what I want to reproduce)

ExcelCapture2.JPG

 

And this is the formating rule they use...

ExcelCapture3.JPG

 

 

Then they email this workbook to various people....my goal is to simply reproduce this in PowerBi so they can just press "refresh", have it import said CSV from a desiginated folder, then they can publish the report online, etc, etc....

 

I think it just comes down to the fact that PowerBI does not handle "conditional formating" in the same way that Excel does...and I was really expecting it to...

 

Thanks!

Isaac

 

 

 

 

 

Hi @ihayes916,

 

What you can do is a workaround based that your data don't have any ID.

 

1 - Add one of the columns to you table and don't summarize it

2 - Add the other columns and select the MAX /  MIN values

3 - Apply the condittional formatting to the MAX columns

4 - Reduce the size of the  first column to hide it

 

 

COND.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



@MFelix

 

FANTASTIC!!!!!!!!   This is definately a step in the right direction for me!!!!!  Thank you!

 

Last question on this....you asked about an "ID Column"....

 

If I added an "Auto ID" column on data import (an incrementing number, starting at 0) would it allow for a different (less of a workaround) solution?

 

Again...thank you!

I was assuming that the ID was something that you would have to make a summarize SUM, AVERAGE to have the values of your score since there is no need to summarize you can use this.

 

The ID will only help you if you need to have it sort by a certain order.

 

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



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.