Forum Discussion
Helper ICreate an x-axis based on summarized data from a inactive relationship
Background:
Not sure if I am overcomplicating things, but I have hid a roadblock based on my understanding of DAX and PowerBI.
I have a user table that feeds an action table. But since then, we have implemented a new table that tracks sessions. It is valuable to us to use the session table, as there is a lot of other metadata and connections that stem from this.
(this is all fake data, as I was trying to debug it without the excess details)
PowerBI:
I would like to be able to create a table that groups users by what month their last action was. I may be going about it all wrong.
Normally I would do this with a calculated column in the user table.
Last Save = CALCULATE(MAX('Saved File'[Saved Date]))
However, the connection is inactive, because of our newer table. I cannot go through the newer table because of the legacy data that is not captured with it.
I tried activating the relationship in Calculate, however this throws an error. Apparently I shouldn't be using USERELATIONSHIP in a calculated column.
Last Save = CALCULATE(MAX('Saved File'[Saved Date]), USERELATIONSHIP(Users[User Id], 'Saved File'[User Id]))
I was hoping to avoid doing this in PowerQuery (which I would normally do out of laziness), because I would like the ability to drill down into the event data (saved file size for example)
Here is a WeTransfer link for my file:
https://we.tl/t-7VQcG5UvvL
Keep learning. Read about Star/Snowflake schemas. USERELATIONSHIP should not be used to try and trick the datamodel into accepting circular relationships. It should be used to switch relationships betwee two tables, like to switch between created date and close date in the same table when linked to a calendar.
Once you clean up your data model you will find that the questions will be easier to answer.
2 Replies
- lbendlin
Super User
Keep learning. Read about Star/Snowflake schemas. USERELATIONSHIP should not be used to try and trick the datamodel into accepting circular relationships. It should be used to switch relationships betwee two tables, like to switch between created date and close date in the same table when linked to a calendar.
Once you clean up your data model you will find that the questions will be easier to answer.
s1Fair point. That makes sense - the solution being "the reason it is not easy to do what you are doing, is because your data model is poorly structured."
Thanks!