Does each report need its own model?

[BCurtis]

Hello,

I'm not new to reporting but I'm new to BI and data modelling in general. I'm just getting started.

I'm hoping to have as few models as possible to power as many reports as possible. I want to avoid maintenance on hundreds of models when things change or break.

Is this the right way to go about it? I'm trying to implement a date table, but I'm very confused on creating the date relationships.

For example, a user table might have "User Created" date

But then an order table might have "Order Placed" date

Then I might have a "Worker joined" date

And a "Worker completed order" date

I prefer to have one model that report builders can build from, but its beginning to look more and more like I need to have one model per report... 

Sorry, I know this question is probably the basic fundementals and I missed something somewhere early on. But can someone point me in the right direction?

Thanks.

[amitchandak]

You need to create a date table(calendar) and it should all possible things you need. Like financial year, month-year, month-year sort etc.

Make sure you have star -schema recommended by power bi too. Refer -https://docs.microsoft.com/en-us/power-bi/guidance/

Powerbi Allows more than one date to join with date calendar and change join based on need using userelation.

Refer  example in Hire and termination : HR-Analytics-Active-Employee-Hire-and-Termination-trend

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[BCurtis]

@amitchandak thanks for the resources, will review and come back.

[Anonymous]

@BCurtis One data model is all you need. Use a star schema. Create a calendar table and then make relationships from the date in calendar table to User Created, Order Placed, Worker Joined, Worker Completed Order. That should get you going.

[BCurtis]

---

@Anonymous wrote:

@BCurtis One data model is all you need. Use a star schema. Create a calendar table and then make relationships from the date in calendar table to User Created, Order Placed, Worker Joined, Worker Completed Order. That should get you going.

---

Thanks for the reply.

Thats easily done, but where I get tripped up, is that each of those tables also has relationships. A User can both place an Order and also become a Worker. So a worker can both place an Order as a customer and a worker can Complete an Order as a Worker.

So what ends up happening is the active relationship becomes convoluted, and I'm afraid that down the line when people make reports off of this model they will be confused why it doesn't act the way it should.

I suppose I need to control this via strictly defined measures using userelationship() and heavily moderating columns that report editors have access to?

[Anonymous]

@BCurtis Can you have the Worker names in one table and the Customer names in a second table. Then you can create a measure of Users who are Workers who Complete Orders and Users who are Customers who Place Orders.