Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Yggdrasill
Responsive Resident
Responsive Resident

Slowly changing dimension type 2

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 ?

CategoryFromTo
A1.1.201831.12.2018
B1.1.201831.12.2018
C1.1.201831.12.2018
A1.1.201930.6.2019
A1.7.201931.12.2019
B1.1.201931.12.2019
C1.1.201931.12.2019

 

I want to use Many-to-Many relationship with the fact table and some kind of a DAX measure

Any idea ?

5 REPLIES 5
RobbeVL
Impactful Individual
Impactful Individual

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

ResortValid FromValid ToRooms
A1.1.201831.12.2018220
B1.1.201831.12.2018150
C1.1.201831.12.201860
A1.1.201930.6.2019225
A1.7.2019 235
B1.1.2019 160
C1.1.2019 40

 

Last column (as a measure ofc ) would be my desired output

ResortDateNightsDesired OutputFormula* 
A1.1.201820091%200/220
A1.1.201921093%210/225
A1.8.2019235100%235/235
B1.1.201812080%120/150
B1.1.201912075%120/160
B1.8.201912075%120/160
C1.1.20183050%30/60
C1.1.20193588%35/40
C1.8.20193075%30/40

*just for showcase

RobbeVL
Impactful Individual
Impactful Individual

just 1 more question, are this always full montly periods? 

No, this could be any daily date for that matter. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.