Hi,
I have a fact table containing appointments for clients and a dim table containing a list of clients with a 'total weekly target hours' column.
I want a table containing client name, total appointment hours from my fact table and the target hours from the dim table.
I am struggling to create a measure for the target hours, can you help?
Thanks, Ian.
p.s., I can't post to the members forum
Could you please upload a file with some example data in both tables and the expected result you want to achieve?
Here is a sample of the visit data (fact table) and the customer data (Dimension Table).
The 'results' sheet show what I want in a power BI matrix, and I need to define a measure for the Target Hours
Did something in Power Pivot. Loaded both tables to the DM with a relationship on the Customer ID. Two simple (tough explicit measures to sum appointment hours and target hours. Added a calculated column for the week number of the appointment, assuming that you will have more than one week of data in reality.
Should work the same in PowerBI. Is that what you had in mind?
Thank You. I am fairly new to Power BI and as soon as I saw your sample, I realised that I had made the basic mistake of using the customer id from my fact table instead of from my dimension table
Thank you!
Great! That's a small mistake. Do it once and never again 🙂
You can always hide the customer id from the fact table so that it doesn't show in the fields to build your visuals with.
TIP: always hide the 'many' side (fact table) of the relationship so that you can't accidentally use it.
