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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
POSPOS
Helper III
Helper III

Color Format for missing date

Hi all,

I have a requirement to color format a shape visual based on missing data, i.e., if data is not available in a coloumn then show in pink , if data is available then show in gray.

 

I have a slicer in the report, and when I select one value, the color formatting is working fine,

POSPOS_1-1699291970313.png


If I select all values, it is still showing in pink.

POSPOS_0-1699291937769.png

Expectation is that, they all should be in gray by default when all values are selected and the colors should change only when the selections are made in the slicer.


Could someone please suggest on how to resolve this?
I have attached a sample pbix file here

Thank you

1 ACCEPTED SOLUTION
jpessoa8
Continued Contributor
Continued Contributor

Hello @POSPOS ,

 

You are using what his called as "Implicit Measure", which means you are doing a calculation for the dates directly with their columns and with this there is no way to do what you want, because it will always have values (as long as their are rows in the table).

 

Instead you should create "Explicit Measures" and add a logic to check if anything in the "Protocol Number" column is selected.


For example, you should create a Measure for the assign date like :

AssignDate = IF( COUNT(TEST[Protocol Number])=1, MAX(TEST[Assigned Date]), BLANK() )

 

With the Distinccount on the TEST[Protocol Number] it will return the number of protocol's select. If and only if the number of protocol is 1, it will return its last Assign Date, otherwise it will return BLANK() . If you want to return values if more than one Protocol is selected, you can use the formula like this :

 

AssignDate = IF( ISFILTERED(TEST[Protocol Number]), MAX(TEST[Assigned Date]), BLANK() )

 

Now, with this measure returning either a date or a BLANK(), you can create another measure for the color formating like :

 

AssignDate_ColorFormat = IF(ISBLANK([AssignDate]), "Format", "Same Color")

 

If the measure [AssignDate] is BLANK(), this measure will return "Format". Otherwise it will return "Same Color".

 

You can find the reviesed pbix file here . I've added a new page using the measures created.

 

Please let me know if this helped you.
Best regards

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@POSPOS now we are talking. As mentioned previously, share csv sample file and I will put it together in the pbix file and share it back with you.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k I have used a sample data to regenerate the issue which is available here

parry2k
Super User
Super User

@POSPOS Keep in mind, you will get many solutions to solve the problem with the existing structure but if you truly want to work with Power BI, you need to follow the best practices. It will go a long way to develop scalable and easy-to-use solution. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k  - Any suggestion on how the data should be for this use case?

 

parry2k
Super User
Super User

@POSPOS shape of your data is not using the best practices. Either you keep on working with the way the data is and then try to solve the issues by writing complex DAX measures or follow best practices, shape the data, and make things easy for the visualization. Choice is yours on how you want to work or go about using Power BI. Good luck!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

jpessoa8
Continued Contributor
Continued Contributor

Hello @POSPOS ,

 

You are using what his called as "Implicit Measure", which means you are doing a calculation for the dates directly with their columns and with this there is no way to do what you want, because it will always have values (as long as their are rows in the table).

 

Instead you should create "Explicit Measures" and add a logic to check if anything in the "Protocol Number" column is selected.


For example, you should create a Measure for the assign date like :

AssignDate = IF( COUNT(TEST[Protocol Number])=1, MAX(TEST[Assigned Date]), BLANK() )

 

With the Distinccount on the TEST[Protocol Number] it will return the number of protocol's select. If and only if the number of protocol is 1, it will return its last Assign Date, otherwise it will return BLANK() . If you want to return values if more than one Protocol is selected, you can use the formula like this :

 

AssignDate = IF( ISFILTERED(TEST[Protocol Number]), MAX(TEST[Assigned Date]), BLANK() )

 

Now, with this measure returning either a date or a BLANK(), you can create another measure for the color formating like :

 

AssignDate_ColorFormat = IF(ISBLANK([AssignDate]), "Format", "Same Color")

 

If the measure [AssignDate] is BLANK(), this measure will return "Format". Otherwise it will return "Same Color".

 

You can find the reviesed pbix file here . I've added a new page using the measures created.

 

Please let me know if this helped you.
Best regards

@jpessoa8  - thank you for the response. I have used few of the steps provided in your solution and it works fine.

parry2k
Super User
Super User

@POSPOS the shape of your data is not correct, and the way you are trying to achieve this is not an efficient way. Share the raw data45.csv file so that I can shape the data in PQ to keep it simple and follow the best practices. Cheers!!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 
This is a sample data I have used to regenerate the issue.
Please note there are other columns/calculations in the actual report.

POSPOS_0-1699296279413.png

Thank you

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors