Relationship between a Sales Table and a Date Table

[RohiniP-26]

Hi All,

Below is my sample data,

Sales Table:

Product   SalesDate           SalesValue

A              12/01/2020         1000

A              02/02/2020          700

B              19/02/2020          1500

C              20/01/2020          500

I have created a date table, Date=calender(min(SalesDate),max(SalesDate)) using this dax formula.

And also i gave a relationship for connecting these two tables,

From table -Date Table (Key(Date))  and To table - Sales Table(Key(SalesDate))

Cardinality -one to many

cross Filter direction - single 

My question is, Why it is automatically changing my 'From' and 'To' table in the given relationship ?

(Ex: In my example, after specifying the relationship, it is automatically changing the 'From' Table as Sales Table(Key(SalesDate) and 'To' Table as Date Table (Key(Date)))

I couldn't understand. Can anyone explain why it is hapenning ?

Thanks in Advance.....

[v-shex-msft]

Hi @RohiniP-26,

How did you From/To table designed? Can you please share some dummy data with raw table structures to help us clarify these and test to coding formula?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[RohiniP-26]

hi @v-shex-msft 

Thanks for helping,

Below is my dummy table with some data.

1) Account Table (General Ledger)

2) Date Table:

calculated using dax: calendar(min('Account Table'[Posting Date]),max('Account Table'[Posting Date]))

And also i gave a relationship for connecting these two tables,

From table -Date Table (Date)  and To table - Account Table (Posting date)

Cardinality -one to many

cross Filter direction - single 

It's automatically changing to (from table: Account Table (Posting date)- To table: Date(Date))

Cardinality -many to one

cross Filter direction - single 

[BA_Pete]

Hi @RohiniP-26 ,

Your description of the relationship change from {Date ONE--Account MANY}, to {Account MANY--Date ONE} is exactly as I described in my original response above - it makes absolutely no difference as they are effectively the same thing.

Is your question trying to understand the software technicalities of WHY Power BI does this, or are you concerned that the amended relationship is incorrect in some way?

Pete

[RohiniP-26]

@BA_Pete 

Yes. As you said, it will not affect my model anyway. But why Power BI doing this ?

[v-shex-msft · Accepted solution]

Hi @RohiniP-26,

AFAIK, Power bi data models are designed based on analysis service tabular.
If you interested in the mechanism of the relationship detect and filter effects, maybe you can take a look at the following links about the AS tabular model, relationships, and the cross filter directions if they help.

Relationships in tabular models 

Delete relationships 

Bi-directional cross filters in tabular models 

Model relationships in Power BI Desktop 

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @RohiniP-26,

You can take a look at the following blog which method the relationship direction and corresponding effects.

What is the Direction of Relationship in Power BI? 

Regards,

Xiaoxin Sheng

[BA_Pete]

Hi @RohiniP-26 ,

It really makes no difference either way, so long as the cardinality is correct for your purposes.

For example:

FROM Sales - TO Date - Cardinality Many:One

is the same as

FROM Date - TO Sales - Cardinality One:Many

As long as the relationship appears as you require it in the model screen (in this instance Date One to Sales Many) then all will work fine.

It can also be the case that there is actually only one of each Date in your Sales table, and therefore Power BI will set the relationship as One:One, in which case it matters even less which table is FROM and which is TO (unless at a later date duplicate dates will be added to your Sales table, in which case fix the relationship correctly now before you get issues later!), although this doesn't sound like your particular scenario.

Pete