Star Schema Question

[Unknowncharacte]

Hi,

I cannot seem to grasp the concept of Star Schema. 

Here is my very basic model at this time. I am not sure it's even enough for a Star Schema right now?

I need to figure out # of HR Complaints open and HR Complaints closed.

I have created a measure for the Closed complaints with the active relationship from 'Fact' Date Closed to 'Calendar' Date.

I do not want to use 'USERELATIONSHIP' measure for the Open Complaints as this dashboard will eventually have row level security and from what I read, 'USERELATIONSHIP' will not work with that. 

So how can I get the # of Open Complaints? Do I dublicate the Fact Table, join on Open Date and do it that way?

Sample Data.pbix

[parry2k]

@Unknowncharacte You didn't answered the full question, how you expect the solution?

Jan 2023 - 5 complaints opened - what you want here, still 80% since you are looking at all opened in Jan which are closed doesn't matter when or which month.

[Unknowncharacte]

This is how I expect to lay it out, just counts. This will also serve as a base for additional calculations. I want to make sure I get the foundation right.

[parry2k]

@Unknowncharacte Let me ask you this question which will lead to the design:

- 5 complaints opened in Jan 2023

- out of 5, 2 completed in Jan 2023, 2 completed in Feb 2023 and 1 is not completed.

Overall  4 completed / 5 total  = 80% completed.

Now think you are viewing this at the monthly level:

Jan 2023 - 5 complaints opened - what you want here, still 80% since you are looking at all opened in Jan which are closed doesn't matter when or which month.

How do you want to see the data at the lowest granulaity, like day/month/quarter/year?

[Unknowncharacte]

Granularity will start on monthly level and will be aggregated up.