Forum Discussion
Fact Table Data Structure for non-business Time Stamp Analysis help
Hi Anonymous ,
1. Try to open the pbix with Power BI Store version.
2. If you want to get the table like the following screenshot, you have to delete the Observation.id column and Behavioral category column, then pivot the Behavioral column.
3. If you want to filter the fact table based on Observation.id column, you need to stay Observation.id column before pivoting the Behavioral column.
Then you can create a relationship between the fact table and a dim table that contains unique Observation.id.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
v-zhenbw-msft , Thank you again for the swift reply!
First off, I was undable to open your pbix file; it says that i need to update to the most recent version of Power BI (I do have the most current version). I tried both 32 and 64 bit versions, even tried backdating to the June Update with no luck.
Second, I was able to recreate the table you made, so thank you for the good instrutions. I did find that the index you addded to the new table did not reference the "DC" data correctly to the correct observation number. I believe I have fixed this by using my ObsNumber column from before and using that as the index column. This does however create some "null" values in the DC column. PLease see the .pbix file in the following link to uderstand what I mean. I have added the DE-ST column and a DC-ST column to test the "null" values.
Third, here are my current questionsthat were brought up with the table structure you built here:
- Will having the Null values in the DC column create any errors? Not all behaviors hapen within every observation, so they need to not interfere with analysis on that observation
- Should this new pivoted table be the main fact table at the center of the star schema layout? If so, I believe I will ahve to connect the tables to be able to filter by event and subject as well?
- I wanted to make sure that this is the best/mst efficient way of creating this analysis; it already seems quite powerful
Thank you again for the help!
Community SupportHi Anonymous ,
1. Try to open the pbix with Power BI Store version.
2. If you want to get the table like the following screenshot, you have to delete the Observation.id column and Behavioral category column, then pivot the Behavioral column.
3. If you want to filter the fact table based on Observation.id column, you need to stay Observation.id column before pivoting the Behavioral column.
Then you can create a relationship between the fact table and a dim table that contains unique Observation.id.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.