Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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,
If I select all values, it is still showing in pink.
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
Solved! Go to Solution.
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
@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.
@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.
@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.
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.
@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.
Thank you
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |