CODEWITHSUNDEEP
t-sqlpowerbidaxpowerquerydata-modeling
variable
variable

Reputation: 9684

How to model a Power BI date table when the database date table has duplicates due to each client having their own calendar?

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.

Upvotes: 3

Views: 854

Answers (2)

Andreas
Andreas

Reputation: 358

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:

DateKey Week_C1 WeekYear_C1 WeekYearLabel_C1 Week_C2 WeekYear_C2 WeekYearLabel_C2
20230101 1 202301 WYC1 01/2023 1 202301 WYC2 01/2023
20230102 1 202301 WYC1 01/2023 1 202301 WYC2 01/2023
.. .. .. .. .. .. ..
20230108 2 202302 WYC1 02/2023 1 202301 WYC2 01/2023
20230109 2 202302 WYC1 02/2023 2 202302 WYC2 02/2023

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.

To transform your existing date table you can use the M query:

let
    Source = Csv.Document(File.Contents("C:\Data\example.csv"),[Delimiter=";", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Client", type text}, {"Date", type date}, {"FYYR", Int64.Type}, {"FYWK", Int64.Type}, {"CORPFYYR", Int64.Type}, {"CORPWK", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Client", "Client2"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Client]), "Client2", "FYYR", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Costco", "Costco_FYYR"}, {"Walmart", "Walmart_FYYR"}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Renamed Columns", "Client", "Client2"),
    #"Pivoted Column1" = Table.Pivot(#"Duplicated Column1", List.Distinct(#"Duplicated Column1"[Client2]), "Client2", "FYWK", List.Sum),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"Costco", "Costco_FYWK"}, {"Walmart", "Walmart_FYWK"}}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Renamed Columns1", "Client", "Client2"),
    #"Pivoted Column2" = Table.Pivot(#"Duplicated Column2", List.Distinct(#"Duplicated Column2"[Client2]), "Client2", "CORPFYYR", List.Sum),
    #"Renamed Columns2" = Table.RenameColumns(#"Pivoted Column2",{{"Walmart", "Walmart_CORPFYYR"}, {"Costco", "Costco_CORPFYYR"}}),
    #"Pivoted Column3" = Table.Pivot(#"Renamed Columns2", List.Distinct(#"Renamed Columns2"[Client]), "Client", "CORPWK", List.Sum),
    #"Renamed Columns3" = Table.RenameColumns(#"Pivoted Column3",{{"Costco", "Costco_CORPWK"}, {"Walmart", "Walmart_CORPWK"}}),
    #"Grouped Rows" = Table.Group(#"Renamed Columns3", {"Date"}, {
        {"C_FYYR", each List.Max([Costco_FYYR]), Int64.Type}, 
        {"C_FYWK", each List.Max([Costco_FYWK]), Int64.Type}, 
        {"C_CORPFYYR", each List.Max([Costco_CORPFYYR]), Int64.Type}, 
        {"C_CORPWK", each List.Max([Costco_CORPWK]), Int64.Type},
        {"W_FYYR", each List.Max([Walmart_FYYR]), Int64.Type}, 
        {"W_FYWK", each List.Max([Walmart_FYWK]), Int64.Type}, 
        {"W_CORPFYYR", each List.Max([Walmart_CORPFYYR]), Int64.Type}, 
        {"W_CORPWK", each List.Max([Walmart_CORPWK]), Int64.Type}
    })
in
    #"Grouped Rows"

I tried it with your example data as a csv:

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

It turns the table from: Source Format to Transformation applied

Upvotes: 1

davidebacci
davidebacci

Reputation: 30219

  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.

Upvotes: 3

Related Questions