Date difference correctly showing blanks when there is no date , but blanks and zeros filter issue

[Pandadev]

I have a date difference column where I compare a date field with todays date , to get a count of months. This is working fine.

But when I filter by zero , if current date month is the same as todays date month , the value in the column is showing as zero. But when there is no data to compare , the value is blank. When I do a filter of a table using a slicer , when I select zero , it also shows the blanks. 

date diff code I use is - 

Date_Last_Contact_Difference = DATEDIFF(Master[Date_Last_Contact] , Master[Todays_Date],Month)

But if I filter by Blank , it correctly shows just the Blanks , I cannot workout why this happening , any help would be much appreciated. Thanks

[v-xuding-msft]

Hi @Pandadev ,

Date_Last_Contact_Difference is a calculated column and it is shown correctly in the table. But it shows blank not the date which is the same as today's month when you add it to a slicer and select 0. Do I understand correctly?

I create a sample to reproduce it. However, it works fine. I think you might share a dummy file to make us understand clearly. If you can't share it, please share some sample data and more details.

I attached my sample that you could point out the difference between your report.

Best Regards,

Xue Ding

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

[Pandadev]

I have the following column measures to get the date difference

Date_Last_Contact_Difference = DATEDIFF(Master[Date_Last_Contact] , Master[Todays_Date],Month)

The issue is that if there is no date in the Master[Date_Last_Contact] field , then the Date_Last_Contact_Difference is blank. But if the month is the same , then the value = 0. 

The visual below is filtered by 0 [zero] but it is showing blanks aswell. if I filter by blanks it only shows blanks. I need to keep the blanks for other stats , so I am unable to just filter out blanks , unless there is a mesure if zero is selected filter out blanks

 

[v-xuding-msft]

Hi @Pandadev ,

I get the message:

This is the default comparison semantics we have in Power BI reports. 0 will match blank. It’s due to legacy from AS equal operator which was used throughout PowerView and at the beginning of PowerBI. The source of the behavior compatibility is in the equality operator described here: https://docs.microsoft.com/en-us/dax/dax-operator-reference. We have not yet added any comparison semantics upgrade in PowerBI.

You can vote for a better behavior to get fixed here: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/33555967-filtering-using-numbers-....

For a workaround, you could change the data type of "Date_Last_Contact_Difference" column from number to text. Then when the column is used as a slicer, it will work fine.

Best Regards,

Xue Ding

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

[Pandadev]

Thanks for looking at this for me , yes having it as a text format resolves the issue , but I am now unable to sort them into numerical order Blank,0 ,1,2,3,4,5,6,7,8,9,10,11

It is currently Blank 0,1,11,12,13,14,15,2,20,3,5,7,8,9

[v-xuding-msft · Accepted solution]

Hi @Pandadev ,

It does have a flaw. If it is not important to filter blank values, you could try to disable "Show items with no data". It will filter numbers  correctly, but when select blank, the entire table will show blank. 

Disable "Show items with no data"Filter 0Filter blank

If this still can't help you, I think you might need to waiting for that idea fixed.

Best Regards,

Xue Ding

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

[Pandadev]

Hi Xue .Thanks for taking time out to find a solution for this. This has now resolved my issue

[v-xuding-msft]

Hi @Pandadev ,

After testing, I think it might be a bug. I have submitted internally. If there is any message, I will post here.

Best Regards,

Xue Ding

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