Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
TeraMaster
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION
AjithPrasath
Resolver II
Resolver II

@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 👍

View solution in original post

2 REPLIES 2
AjithPrasath
Resolver II
Resolver II

@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 👍

@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?  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors