In the database I have a date table. The date table has got a calendar for each client. So for example say there are 10 clients, and the calendar has dates for 5 years, then there are 10 * 5 * 365 records in this table.

Example:

 -------- ------------ ------ ------ ---------- -------- 
| Client |    Date    | FYYR | FYWK | CORPFYYR | CORPWK |
 -------- ------------ ------ ------ ---------- -------- 
| Costco | 01-06-2022 | 2023 |    1 |     2022 |     22 |
| Walmart| 01-06-2022 | 2022 |   22 |     2022 |     22 |
| Costco | 02-06-2022 | 2023 |    1 |     2022 |     22 |
| Walmart| 02-06-2022 | 2022 |   22 |     2022 |     22 |
| Costco | 03-06-2022 | 2023 |    1 |     2022 |     22 |
| Walmart| 03-06-2022 | 2022 |   22 |     2022 |     22 |
| Costco | 04-06-2022 | 2023 |    1 |     2022 |     22 |
| Walmart| 04-06-2022 | 2022 |   22 |     2022 |     22 |
 -------- ------------ ------ ------ ---------- -------- 

When I import this table into Power BI, then it doesn't allow me to mark it as date table (due to duplicates).

Since it has duplicate dates, when I create a relationship from this table to the fact table, it gets created as a M:M relation (msdn documentation mentions that M:M reduces the model performance).

On the report I have a slicer (on client name from this date table) to ensure that only 1 client is selected, so that the calendar then doesn't have duplicates.

I cannot use DAX date/time intelligence function because this table cannot be marked as a date table.

To solve this I could create 5 date tables from that table, mark them all as date tables and connect all of them to the fact table. Then have 1 report page per client. But I don't want to do this as I don't want to create separate report page per client.

What is the correct way to model such a date table in this scenario via SQL or PowerQuery or PowerBI? The end goal being that the table can be marked as a date table so that I can use date/time intelligence DAX.

CodePudding user response：

1. Time intelligence functions won't work without a proper date table. In addition a many-to-many should be avoided if at all possible as it will make the rest of your DAX very complicated.
2. A date table is by definition just a dimension with no duplicates and a full range of dates covering an entire year. You can create this dimension from your fact table in PQ.

The real question is why does each client get their own calendar? What is the difference between client 1's calendar and client 2's calendar?

Many to many relationships are "limited" relationships and do not behave like normal one-to-many relationships in a whole host of ways (e.g. no blank row for missing dimension keys). It is a very detailed subject and you're best reading from the experts here: https://www.sqlbi.com/articles/strong-and-weak-relationships-in-power-bi/

Regarding having a different calendar table per client, I think I understand now and the solution might be complicated. If you only have a few clients, I would be tempted to create these calendars as additional columns of a standard date table. e.g. Date - Day - Month - Year - Etc - Client Type 1 FY Start, Client Type 2 FY Start

Ideally there is some commonality between each client so you can genericise the special columns as I have done with Client Type rather than individual client.

It is common in PBI to create dimension tables from a fact table. You do this by referencing the fact table, removing other columns, removing duplicates and then you are left with a dimension table to join to your fact table in the model.

CodePudding user response：

To reduce the count of calendars, I recommend to abstract the different calendars from the customers. For example:

• Customer A uses Calendar A
• Customer B uses Calendar B
• Customer C uses Calendar A
• Customer C uses Calendar A

Then you should hopefully end up with just a couple of calendars.

In the next step I would add columns to the date table according to the calendars. If the calendars just differ in week information this would be WeekNumber, WeekYear and so on.

Example:

You end up with a date table with unique dates, but more columns. You will be able to mark this table as a data table, but you have to build up your reports according to the customers calendar, by slicing and filtering the appropriate columns.

This might be a great use case for field parameters.

CodePudding user response：

One way to model this date table in Power BI would be to use Power Query to transform the data in a way that eliminates the duplicate dates. Here are the steps:

Load the date table into Power Query Add a column that concatenates the date and client name Group the data by the new column and use the "Group By" function to keep only one row per date and client Remove the new column Mark the table as a date table in Power BI This way, the table will not have duplicate dates and can be marked as a date table. Once the table is marked as a date table, you can create a relationship between it and the fact table and use DAX date/time intelligence functions. Additionally, you can use the client column in a slicer to filter the data by client. If you wish to avoid creating multiple date table, you can use the existing date table and create a measure with a dax formula to aggregate the data by client. You can use the client column in a slicer to filter the data by client.

Another way, you can create a calculated column that concatenates the date and client, and use this column as the date column in your relationship.

It will be a good idea to test and compare the performance of both solutions, to select the best suited for your case

Another option to consider would be to create a separate date dimension table in your data source that has a unique date for each day, and then create a bridge table that links the date dimension table to the client table. This way, you can maintain the client's calendar information, as well as having a unique date for each day that can be marked as a date table in Power BI. This bridge table can be created in Power Query or SQL and can be used to join the fact table with the date dimension table and the client table, allowing you to use DAX date/time intelligence functions and filter by client. However, you will need to test the performance of this solution as well, as it may have an impact on the performance of your report.