Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Not sure what to call this but I have a fact table with Category {1...N} and other sized data like Amount and Date etc.
How to I properly work with this kind of dimension table ?
Category | From | To |
A | 1.1.2018 | 31.12.2018 |
B | 1.1.2018 | 31.12.2018 |
C | 1.1.2018 | 31.12.2018 |
A | 1.1.2019 | 30.6.2019 |
A | 1.7.2019 | 31.12.2019 |
B | 1.1.2019 | 31.12.2019 |
C | 1.1.2019 | 31.12.2019 |
I want to use Many-to-Many relationship with the fact table and some kind of a DAX measure
Any idea ?
Hi,
What is the actual output you are looking for?
Actual output is a ratio.
I have daily fact data vs resorts with amount of hotel rooms they offer.
Sometimes the amount of rooms changes when they add rooms after reconstructing or something else so Resort A which had 60 rooms to offer now has 75 from 1.st of June 2019 and so forth
The Occupancy ratio is highly depentant on the available rooms : )
To make this problem easier to understand, here's my Dimension table
Resort | Valid From | Valid To | Rooms |
A | 1.1.2018 | 31.12.2018 | 220 |
B | 1.1.2018 | 31.12.2018 | 150 |
C | 1.1.2018 | 31.12.2018 | 60 |
A | 1.1.2019 | 30.6.2019 | 225 |
A | 1.7.2019 | 235 | |
B | 1.1.2019 | 160 | |
C | 1.1.2019 | 40 |
Last column (as a measure ofc ) would be my desired output
Resort | Date | Nights | Desired Output | Formula* |
A | 1.1.2018 | 200 | 91% | 200/220 |
A | 1.1.2019 | 210 | 93% | 210/225 |
A | 1.8.2019 | 235 | 100% | 235/235 |
B | 1.1.2018 | 120 | 80% | 120/150 |
B | 1.1.2019 | 120 | 75% | 120/160 |
B | 1.8.2019 | 120 | 75% | 120/160 |
C | 1.1.2018 | 30 | 50% | 30/60 |
C | 1.1.2019 | 35 | 88% | 35/40 |
C | 1.8.2019 | 30 | 75% | 30/40 |
*just for showcase
just 1 more question, are this always full montly periods?
No, this could be any daily date for that matter.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |