Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |