DAX issue with date filters

[Anonymous]

Dear Team,

I have an order table and user table which are linked by user id.Both of these tables have a date column.I created a master calendar inorder to fill the missing dates and its linked to user table.Now when I select a particular year,both the date columns gets filtered,but if I filter using months also , only user table date column filters correctly and order table column shows other months date too.Can somebody tell me the reason why its happening?Thank you for your time.

[dedelman_clng]

Can you share a picture of the data model?

[Anonymous]

[v-sihou-msft]

@Anonymous

In this scenario, your calendar table is connecting to User table only .When you select in slicer, it will filter the User table based on data, then filter the Order table based on the Id.

I can't understand why you need to have date column in User table. In this model, your Order table should be fact table, both User table and Calendar table should be dimension table. Since you already have "many to one" relationship between Order table and User table on Id column, the date column in both table should have no direct relationship. So your Calendar table should connect Order table only.

Regards, 

[Anonymous]

@v-sihou-msft

Date column in user table is needed to find out the user's date added

[dedelman_clng]

Hi @Anonymous,

In your scenario, the filter on Date directly affects the Users table, which then directly affects the orders table.  If a user with a "created date" in Mar 2017 has orders inside and outside that month, all of the distinct dates on which that user made an order will be shown when slicing the date.

What is your end goal in the report?  You will probably need to make two relationships for Orders - one to Users and one to Master Calendar.  One of those relationships will be "deactivated" (dotted line in the pic below indicates a deactivated relationship) and when you need to use it in a calculation you'll have to add the USERELATIONSHIP function to temporarily enable it.

For example, if you want the orders sliced by date, then the relationship between Orders and Master Calendar will need to be the active one, and the relationship between Orders and Users will be deactivated.  Like I said, it all depends on the end goal for your calculations, visualizations and reports.

Hope this helps

David