Forum Discussion
Relationship between a Sales Table and a Date Table
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.....
Hi Anonymous,
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
Bi-directional cross filters in tabular models
Model relationships in Power BI Desktop
Regards,
Xiaoxin Sheng
7 Replies
- BA_Pete
Super User
Hi Anonymous ,
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
Hi Anonymous,
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
hi Anonymous
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
Hi Anonymous,
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
