Many to one relationship - not filtering correct

[Anonymous]

Hi all,

I do not have much experience in PowerBi and I am dealing with the following problem:

Two tables, connected on a MANY to ONE relation based on Date column.

1. Tabel 1: Sales table

Date (dd/mm/yyyy) - MANY

OffertNo

Value

Customer

…

Tabel 2: Calendar

Date (dd/mm/yyyy) - ONE

The Date column from Calendar table is a filer applied to all pages from my report and connected with other tables.

The problem is: my report shows sales outside of the selected date.

For example: 01.01.2020 – 10.01.2020 there are 5 OffertNo in the Sales table by my report shows 18 results (including sales from February, March).

I made a test based on a calculated column DayNoYear created in both tables and the problem is not solved.  

Please advise.

Thank you,

A.B.

[amitchandak]

@Anonymous , if the relationship is correct. Then this can only happen If you have use all in your formula or you have switched of interactions

https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/

If not these 3. then, please share pbix, after removing sensitive information 

[Anonymous]

After many checks I noticed that when the date 01-01-2020 is setted as start date to filter data, my report goes wrong. There are included offerts/rows that are not registrated on this date. Filter on 31-12-2019 or 02-01-2020, the data is correct.

As a details: Table 2 Calendar starts from 01-01-2020, table 1 Sales has data before 2020. The end user are intrested in data starting from 01-01-2020.

Any advice is much apreciated. 

Many thanks, 

A.B.

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

[Anonymous · Accepted solution]

Thank you for support. I found the problem: my calendar table (based on data from another table) was missing the weekends days and the days in the future. In my Sales table were data's registered on the weekends days and in the future. The records on weekend days and the ones in the future are showed in the rapport on 01.01.2020. I am gone insert an year calendar.