Why my star model seems not working

[Shineccx007]

Hi,

I have a star model with calendar date and aggregate by date column. starting from 2020-1-1, continuously till today. I have week, month, Year-week and Year-month column. Table created by Power query.

I have a table include labor daily clock in and clock out time, with employee ID and working hours, I am able to calculate per employee daily hours spend. I need to aggregate the working hours per day, per week, per month and per year.

I have a table on quantity of product produced every day.

I have a table on bi-weekly paid hours, regular hours and overtime hours per production line.

All these table are connected to each other by the date column. When I create the relationship, I saw my calendar date is not showing continously.

I want to aggregate by day, by week, by month, by year to see regular hours and overtime hours spend per case product produced.

But my calendar date column is not showing on matrix when I get the measure to it.

What shall I do then?

[cengizhanarslan · Accepted solution]

1) Make sure you have ONE proper Date table

Your Date table must have:

• a daily column of type Date (no time)

• unique values (no duplicates)

Then in the model:

• Model view → Date table → Mark as date table (choose the daily Date column)

2) Confirm relationships are correct 

For each fact table (labor daily, product daily, bi-weekly payroll), check:

• Relationship: DimDate[Date] (1) → Fact[Date] (*)

• Cross filter direction: Single

• Relationship: Active

• Data type on both sides: Date (not DateTime, not text)

3) Your “bi-weekly paid hours” table is a different grain

If that table is bi-weekly per production line, it likely has a period start/end or a pay period ID, not a daily date.

Connecting it directly to DimDate[Date] usually breaks the model.

Better options:

• Create a PayPeriod dimension (PayPeriodID, StartDate, EndDate) and relate payroll facts to that.

• Or, if you need it by day/week/month, you must allocate those bi-weekly hours across dates with DAX measures and use in your visuals, otherwise daily aggregation will be misleading.

[Shineccx007]

Hello,

On the PayPeriod Dimension,do you suggest me to get it a different dimensiont able or use DAX to aggregate them? How can I assign different PayPeriod to the date on the calendar table? I think I am stuck in this step. Can you please offer more detail?

Thanks!

Shine

[cengizhanarslan]

If you are able to create a dimention with a proper unique ID column I would recommend this as the first option for sure.

[Shineccx007]

This is how the table relation connected.

Under Calendar table, I have these columns.

I am trying to aggregate by Date, Pay Week, Month and Year to analyse the quantity produced and total labor hours. When it goes to Payweek, my Dax looks not working properly. By when I drag each week, it showed like partial make sense.

But the unpaid weeks I also need it to be calculated.

Where is wrong? Why I cannot get the data aggragated?

[Shineccx007]

Ang why by date my total hours and my total distinct count can not be aggregated?

TotalHours = SUM('ADP Labor cost'[TotalHours])

TotalSku = DISTINCTCOUNT(FinishedProdOrders[Item_No])

[pankajnamekar25]

Hello @Shineccx007 

The issue is with how your Date table is set up. You need to mark your calendar as an official Date table in Power BI. Make sure you have a single Date table connected to all fact tables with a one-to-many relationship. Use only the Date table’s fields (like day, week, month, year) in your visuals. Make sure all aggregation happens through measures, not columns. Once that’s set, your date aggregations will work as expected.

Please share model image if possible to explore it

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.


Thanks,


Connect with me on:

LinkedIn

[krishnakanth240]

Hi @Shineccx007 

Mark the calendar as a Date table. Use one common Date table for all fact tables. Relationships must be 1 to many (Date table to Fact table) on a date column. With one to many relationship between the dimension and fact table, Star Schema model works. Do not join fact tables to each other instead create a bridge table. Ensure to create measures instead columns for aggregation.