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.
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,
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.
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.
