Color Format for missing date

[POSPOS]

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

[parry2k]

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

[POSPOS]

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

[parry2k]

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

[POSPOS]

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

[parry2k]

@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!

[jpessoa8 · Accepted 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]

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

[parry2k]

@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!!

[POSPOS]

@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