Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I have 3 tables, a Resident Location Table (Fact Table), which has columns for Person ID, Bed ID, Enter Date (the date a person takes up a bed) and an Exit Date (the date a person leaves the bed), a Location Table (Dimension Table) which has columns for Bed ID and Facility, and finally a Residents Table (Dimension Table) which has columnns for Person ID and other personal information.
What I am trying to do is determine how many people are in a Facility on a particular day, and have solved this by creating a new column in the Resident Location Date for that particular date i.e. all dates in this column are the same, and then creating another new column in the Resident Location Table that uses an IF statement to determine if the date is between the Enter Date and the Exit Date, to include the person in the Pivot, otherwise to exclude the person. A person can move from one facility to another and back depending on a variety of circumstances.
I am sure that this is an extremely cumbersome way of doing this calculation, and hopefully somebody can show me how to use a dax measure/s that will accomplish the above, so that I can use the Time Intelligence Functions to create some useful reports.
Thanks
Peter
You want Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |