Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So i have a fact table which contains date, sessionid and userid. I have a dim table which contains sessionid and license status. Bothe are connected by sessionid by one to many relationship. I want to track overtime changes by (granularity of day) when was a single persons license status was changed ( day) and also changed to what. (see output table columns in red)
I have 5 licnese type blank(which means no status has been recorded),free, pro, semi-pro, max. I want to track changes for every userid. solutions in any form would be appreciated thank you
Input tables
Fact table | ||
date | sessionid | userid |
01.01.23 | abc | person1 |
05.05.23 | cdc | person2 |
0.4.06.23 | kla | person1 |
dim table | |
sessionid | license status |
abc | Free |
cdc | blank |
kla | Pro |
Output table
dim table | |||
sessionid | license status | status changed | date changed |
abc | Free | 01.01.23 | |
cdc | blank | ||
kla | Pro | Free-> Pro | 0.4.06.23 |
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |