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

Conditional Formatting In A Table

Hello,


This is a very unusual request, but I am not able to replicate something that I have already done.

 

I have a small table with three fields (Unbilled-Recent; Unbilled vPM, Unbilled vPY). As you can see, the two last columns are conditionally formatted. I am not able to replicate this anymore in the new table (if I want to have the same with other values, it doesn't work). It works only if I add a new column with a Period (Month, Quarter, Date, etc.). But if I want to have only values as standalone columns like in this case, it doesn't work (i.e. there is no conditional formatting applied, so arrows do not show up).

 

Even if I am creating a new table, adding these three measures and try to format them conditionally once again, it doesn't work. 🙂 

 

Can anyone help - what is going on in here?

 

Below are the formulas:

Unbilled-Current-Month (same as Unbilled-Recent) = (calculate(sum('All BPOC Reports'[Unbilled]),filter('All BPOC Reports','All BPOC Reports'[Date01]=max('All BPOC Reports'[Date01]))))/1000  
 
Unbilled-Previous-Month =
VAR MaxDate = max('All BPOC Reports'[Date01])
VAR MyDate = calculate(max('All BPOC Reports'[Date01]),'All BPOC Reports'[Date01]<MaxDate)
RETURN
calculate(sum('All BPOC Reports'[Unbilled]),'All BPOC Reports'[Date01]=MyDate)/1000
 
Unbilled-Previous-Year = calculate(sum('All BPOC Reports'[Unbilled]),SAMEPERIODLASTYEAR('All BPOC Reports'[Date01]))/1000
 
Unbilled vPM = ([Unbilled-Current-Month]-[Unbilled-Previous-Month])
 
Unbilled vPY = [Unbilled-Current-Month]-[Unbilled-Previous-Year]
 
Here is a part of my dataset (it contains projects from 2020 and 2019):
 
REGIONPROJEKTNRPROJEKTNAMERISKPOCDELTAUNBILLEDDEFERREDDATE01PERIOD

VAST

123456lalaland0ja0%0110003/31/202003/27/2020
VAST123457whatever9888ja0%1080006003/31/202003/27/2020
VAST771814does10000ja3%100080602/29/202002/28/2020
NORR555444not90000no5%20000400002/29/202002/28/2020
NORR444311matter98666no10%40000133331/31/202001/31/2020
NORR111441at all55000no22%50666500001/31/201901/31/2019

 

Unbilled01.JPG

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please delete all the default rules and then add new rules. Then, it works. Amazing.

icon.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

10 REPLIES 10
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Measures are based on context so when you have the calculation of the measure without reference you are filtering it will not return the icons.

 

It is suggested to add one column to the table. Or you could try to use Card visual with conditional format of background color.

background.jpg

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@Icey I think I understand, but in the example I pasted it is a table, these are not measures.

 

So, it is a table with three columns. And it works.

 

The problem is that when I want to replicate this (i.e. create a new table with the same three columns and use the same conditional formatting), the measures do not appear.

 

So, accidentally I made this work and I am more than happy, but when I want to use the same methodology in a new project, I am not able to...

 

 

Icey
Community Support
Community Support

Hi @Anonymous ,

 

If your table visual is made like below, conditional formatting will not work. 

table.PNG

 

I don't know how you can achieve below, because it is logically impossible.

 


 

Unbilled01.JPG

 


If you don't mind, please create a dummy PBIX file, removing sensitive information and replacing real data, for specific test.

It is suggested to upload the file to OneDrive for Business and then paste the link here.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

thank you @Icey for spending so much time and trying to help me out.

 

Below is my pbix, first time i am sharing pbix and first time i am using one drive, hope all is ok 🙂

 

 

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Oh, I have no access to your file. It is suggested to upload your file to OneDrive for Business and then paste the link here. 

 

 

Best Regards,

Icey

Anonymous
Not applicable

how about now?

 

CLICK

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Please delete all the default rules and then add new rules. Then, it works. Amazing.

icon.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

thank you so much! 🙂

Greg_Deckler
Super User
Super User

Can you post screen shots of your conditional formatting rule dialogs?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

sure, please see below:

U1.JPGU2.JPG

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.