Change Model to Star Schema

Power BI
1

Dear Community:

I am looking for advice on how to go about changing my model to a Star Schema and still be able to filter on FY23,FY24 and FY25? I am asking how can I obtain the same results as my attached PBI by changing some aspect of the model? Currently I am using two disconnected tables with a many to many relationship to achieve an allocation qty and $ amount. I anticipate I will have more items than the three presented here and most likely I would have three separate FY prices to allocate by. Any advice on how to achieve the Star Schema and maintain the accurate result by year is what I am hoping to achieve, without separate measures for each item,

image
image1119×591 33.1 KB

year and allocation amount.

image
image1119×591 33.1 KB

Thanks in advance for any input on this issue!!

https://drive.google.com/file/d/100lvi-5CGMbPpKj7MY7G5giInCrfHZt6/view?usp=sharingAllocation model

1 Like
2

@Whitewater100 ,

The way I typically handle these sorts of granularity mismatches is to allocate down to the day level in Power Query, join in a compliant star schema, and an aggregate up to the appropriate level using simple aggregate DAX measures.

I did a webinar walking through step-by-step how to implement this approach last Fall. You can find it on the new platform at:

https://app.enterprisedna.co/app/virtual-events/167

I think this should get you where you need to go.

– Brian

3

Hi Brian:

Thank you very much for your reply and pointing me in the right direction. I will watch the seminar now and reply again. I really appreciate how you have always been such a great and prompt resource!

Thank you again.

1 Like
4

Hi Brian:
That video is great for addressing granularity! My question is how can I make this model a Star Schema and product the same measure results while using a FY slicer? Everything on this particular report is only expressed on an annual basis, so granularity is annual only.

In other words how can I possibly change my model to Star Schema and not to have the many to many relationships between the allocation and price scenario tables. Since the base figures do not change from year to year (just the allocation percent and pricing by year).

Does that make sense?

Thanks Brian! Bill

5

Hi @____! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!