How to connect these two tables according to month & hour?

[TeraMaster]

HI,

I'm trying to connect one fact table (consumption data by hour ) with another table with different labels  according to month & hour. It's like: "My consumption of 01:00-02:00 PM must be classified according to mounth & hour. With the table 2 I can labelled It."

May I make a Calendar (Dim table) & HOUR(Dim table)?

Yours sincerely, 

TeraMaster.

[AjithPrasath · Accepted solution]

@TeraMaster ,

Yes, you can create a Calendar (Dimension) table and an Hour (Dimension) table to help with labeling and classifying your consumption data based on month and hour.

Here's how you can create these tables:

1. Calendar (Dimension) table:

   • Create a new table called "Calendar" with columns such as "Date," "Year," "Month," "Day," etc.
   • Populate the "Date" column with a range of dates that covers your consumption data.
   • Use Power Query or DAX functions to extract the necessary information from the "Date" column, such as year, month, and day, and populate the respective columns.

2. Hour (Dimension) table:

   • Create a new table called "Hour" with columns such as "Hour," "AM/PM," "Label," etc.
   • Populate the "Hour" column with a range of values from 1 to 12 to represent the hours of the day.
   • Populate the "AM/PM" column with values "AM" and "PM" to indicate the time period.
   • Create a "Label" column where you can define the desired labels for each hour and AM/PM combination. For example, "01:00-02:00 PM" can have a label like "Afternoon."

Once you have these Dimension tables set up, you can establish relationships between them and your Consumption (Fact) table. The Calendar table will be linked to the date column in your Consumption table, while the Hour table can be linked to the hour or time column in your Consumption table.

By creating these Dimension tables and establishing relationships, you can use the attributes from the Calendar and Hour tables to label and classify your consumption data based on month and hour in your visualizations and calculations.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[TeraMaster]

@AjithPrasath ,

Thanks a lot for clarifying my mind.  That's a good point.  

Let me ask you some lines: In the case we've got our Consumption table connected to both Calendar table & Hour table, is any form for connecting our fact table with the Labels table?