Date column produces blank when used as a slicer dropdown

[blueunicorn]

Hi - I have two tables A and B. Table A has a date column. I created table B using Distinct('A'[Datecolumn]) to use it with some other measures. Now, when I use it as a slicer, I get a blank. But there are no blanks in the table. Please help me remove the blank without suppressing it manually. Thanks!

[Poojara_D12]

Hi @blueunicorn 

To remove the blank value from your slicer:

1. Create a new table excluding blanks:
   DAX: DateTable = DISTINCT(FILTER('A', NOT(ISBLANK('A'[DateColumn]))))

2. Use DateTable in your slicer instead of the original table.

This will ensure the slicer doesn't include any blanks automatically.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[Anonymous]

Hi @blueunicorn 

Thanks a lot for OwenAuger's prompt reply.

OwenAuger makes sense, there may be an inappropriate relationship between the tables. Please make sure:

Make sure the relationship between table A and table B is set correctly. Make sure there is a one-to-many or many-to-one relationship between the date column of table A and the date column of table B.

And, you can modify the code to:

TableB = FILTER(DISTINCT('TableA'[Date]), NOT(ISBLANK('TableA'[Date])))

Make sure there are no null values ​​in table A that affect table B.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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

[OwenAuger]

Hi @blueunicorn 

It sounds like an automatic blank row has been added to Table B.

This happens if Table B is on the 1-side of a strong relationship with values in the column of the table on the other side of the realtionship that do not exist in Table B's Date column, making it an "invalid relationship" (see here).

Are there in fact any relationships involving Table B (presumably with Table A and any others)? And do the values in the related columns all exist in Table B?