Date table linked to 2 tables

[EVEAdmin]

Hi all,

in my query, I have the following main tables:

• customers table
• invoices table
• products sold table

I then created a Datetable, with a record for each day, from the date of the first invoice until Today() and successfully related it to the Invoices table.
Then, I though I could relate the same Datetable to the Customers table too and use it run time based reports, on Customers. Is it ok to use the same Datetable? Would you recommend using a separate date table for any reason?
Thank you.

[MFelix]

Hi @EVEAdmin ,

You can add multiple connections between tables, this can make your data model more complex butit's not proibitive, depending on the size of the model you can also have some performance issues, but as refered all depends on what you are calculating.

In your case and looking at the model the only thing you need to take into attention is that when you are making visualizations based on the connection between the customers table and the date table you need to create measures based on USERELATIONSHIP since the relation between both tables is inactive.

Regards,

MFelix

[EVEAdmin]

@MFelix thank you

Can you please expand on the "create measures based on USERELATIONSHIP" ? 
An example would be appreciated.

Meantime, I deactivated the relationship between the customers table and the invoices table, which was based on the customer ID and actually prevented me from activating the relationship between the customers table and the date table. That relationship is active now.

[MFelix · Accepted solution]

Hi @EVEAdmin ,

Let's assume you have a sales table with 3 simple columns - Sales Quantity, Sales Date, Sales Order Date.

If you want to create a visual that compares the Sales quantity by sales date and Sales order, depending on the column you use on the chart will give you the calculation based on the month of order or of sales, bu having this information in a single table you are not abble to calculate the two measures so by order date or by sales date.

In this case you can use a calendar table to make the link however you can only have one active relation between two table at once. Then you will get a active relation and an inactive:

In this case since the active relationship is the Sales Date - Date all calculations withouth any additional syntax will be made with that date in use.

If you want to calculate the Sales Quantity be Sales Date and by Order date in the same visual you need to create two measures:

Sales by Date = SUM(Sales[Quantity])

Sales by Order Date = CALCULATE(Sales[Sales by Date]; USERELATIONSHIP(Sales[Order Date];'Calendar'[Date]))

As you can see the second measure is using the first measure but with the USERELATIONSHIP we are "activanting" the relationship between both table but this time based on Order date and not sales, so if you place both measures on a visual you will get:

If you compare the image above you will see it's the matching between the two previous bar charts.

See attach a PBIX file with this example and with two table (one with the relationship another without) so you can have the comparision.

If you google it on DAX USERELATIONSHIP  or DAX inactive relationship you will find further examples and uses for this.

Check also this post:

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Regards,

MFelix

[EVEAdmin]

@MFelix thank you appreciated