Factless fact tables

Hi!

Consider the following example. Dimension tables with information about users and groups. The startdate column indicates when the user/group was created. I want to analyze how many users and groups that were created in each month. I could either
A) Use only DAX without any relationship to the date dimension
B) Create inactive relationships as in alternative 1 and use USERELATIONSHIP
or
C) Create a factless fact table containing only the date information as in alternative 2. This is also what is done in the UserGroup example.
D) Create duplicate calendar table with a relationship only to the the user and group tables

Option C seems the “cleanest” to me but a bit of an overkill at the same time.
What’s your thought?

Hi @Frede, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @Frede

Thank you for explaining in detail. The best option in this scenario would be to create a date table in power query and make active /inactive relationships as per the need. It might be a god idea to go through this video by Sam,

Date Table

You can use this code in the advanced editor of power query

Power BI Date Table code.txt (2.8 KB)

Regards

Najah

This look quite good to me

image

Maybe you could just lose these top two table though and move the Group Members up the top lookup table layer. Just consolidate all the information into the on GroupMembers table.

You can then use a function called TREATAS to create a virtual relationship to the factGroup table if you need to eventually for any calculations on that table.

For more info on TREATAS see here

https://forum.enterprisedna.co/search?expanded=true&q=%23enterprise-dna-online%20TREATAS

Chrs
Sam

Hi,

Thanks for your reply. Are you suggesting to add all columns from user and group table into one so the columns would be.
UserID
UserName
Gender
UserStartDate
GroupId
GroupName
GroupDescription
GroupStartDate

I have watched the TREATAS video earlier and it seems powerful.

Sorry for the delay, but yes I would try this. Consolidate and simplify as much as you can.

Sam