Data source

fact_codes

dim_headcount

Preparing data

fact_codes

Date - date (not datetime, not datetimezone)

Staff number - number

Code - text

= Table.TransformColumnTypes(Source,{{"Date", type date}, {"Staff Number", Int64.Type}, {"Code", type text}})

dim_headcount

Date - date (not datetime, not datetimezone)

Headcount - number

= Table.TransformColumnTypes(Source,{{"Date", type date}, {"Headcount", Int64.Type}})

Preparing model

Common field in both tables are dates, so we need calendar to connect them correctly. You can use any calendar that you want, but my recommendation is to create dim_calendar tables in DAX using Modeling > New Table option.

dim_calendar = 
CALENDAR(
    MIN(FIRSTDATE(fact_code[Date]),FIRSTDATE(dim_headcount[Date])),
    MAX(LASTDATE(fact_code[Date]),LASTDATE(dim_headcount[Date]))
)

This DAX creates a table that holds all the dates between minimum and maximum date in fact_core or fact_headcount table. With this approach, you will prevent situation, when new dates will come to eg dim_headcount table that won't appear in the fact_code and vice-versa.

Important note! Make sure to select new created column "Date" and change Type from Datetime to Date.

Notice: for displaying data I am using yyyy-mm-dd format, but you can change it as you prefer.

Preparing relationship

In the model view connect:

• fact_table[date] to dim_calendar[date] - Power BI should automatically find out that this is many to one relationship
• dim_headcount[date] to dim_calendar[date] - Warning! You need to change this relationship, because Power BI will set that to be 1:1 and Cross-Filterdirection set to Both. Change it to be Many to One, from dim_calendar to dim_headcount. This changes are marked as green on the screenshot.

Bonus #1 - you can turn off the Dates in fact_table and dim_headcount table clicking on "eye" next to them. Remember to always use Date column from dim_calendar table. Disable visibility of other dates saves you from making mistakes. This changes are marked as blue on the screenshot.

Bonus #2 - Before next step: I always create a new table that holds my measures in one place. You can use code to create a dummy table like this:

DAX = ROW("DAX","DAX")

Protip: After creating at least one measure in that table you can make this "DAX" column invisible like the dates before.

Screenshot before creating measures:

Preparing measures

Total not avaliable Staff = COUNT(fact_code[Staff Number])

Headcount measure = SUM(dim_headcount[Headcount])

 Not that I cannot make a measure with name Headcount, because we already have a Column with that name. You need to change that name in the source table or change the name of a measure that meets your needs.

% of not available = DIVIDE([Total not avaliable Staff],[Headcount measure])

In this measure we do not multiply this by 100! If we want percentage we're only changing format. You can change the format by clicking on the measure and clicking on the icon of % or selecting Percentage format from list.

Last step - putting all together

Create a table and put there the measures that you've created and a date from dim_calendar table.

Final result:

PBIX FILE: https://we.tl/t-yf6SecDkCD