Date filter does not work as date table and data table have 1:1 relationship

[BBrg]

Hello,

I've been struggling with this for a while and therefore decided to ask for help.

I am trying to achieve the basic task of importing data from two Excel table into PowerBI, connect them by the "Date" column to a Date table that I created and then use a Date filter to filter the date that different visuals show. However, I have the following problem:

When I connect the two tables to my "Date" table, one of the relationships is 1:1 instead of Many to 1. This already looks strange..

When I go on the report and use the date filter, all visuals containing data from the "Process" table shows "Blank". So the date filter does not work but I do not understand what.

Some additional info:

- the hierarchy in the Process table dissapears when I create the connections.

- all date columns are formated as below:

Any idea about how I can solve this? Feel free to ask more clarifying questions. Thanks!

[HarishKM]

@BBrg Hey ,

You can use below code .

Formatdate = FORMAT(demo[Period],"yyyyMMdd") 

then try .

or 

Use summarize function 
Like 

date = summarize(table name,
                                     date[date])

[BBrg]

Hi,

I used the first option but not sure how that is supposed to help. I now have a new column that looks like this:

Thanks!

[amitchandak]

@BBrg , Click on the relationship, it will open a pop-up, Change it to 1 -M ( Date is on one side. Prefer single direction too)

Try this and check.

[BBrg]

Thanks, @amitchandak . So I managed to change this, as you advised.

However, the hierarchy from the "Process" table is still missing. And the date filter is still not working.

If I add the "Referral created date" as a filter, just to see what it returns, I see the following:

This looks very strange, right? Basically, it shows every single line instead of unique dates.

Any other suggestions? Thanks!