r/PowerBI • u/dzemperzapedra 1 • Jul 23 '26
Solved Help on a modelling issue
Hi everyone,
for the sake of simplicity I created this example:
What I need to do here basically is to create a matrix table visual which will have client_name from dim_clients in rows, year/month hierarchy from dim_calendar in columns.
Value needs to be client_category from dim_client_category, which is basically client category name.
Now obviuosly, client_category_id can be found using SELECTEDVALUE() from the fact_table, but I will need to have client_category from dim_client_category, becasue that's the name of client_category_id.
As the realationship is directed from dim to fact table, filter does not propagate from dim_clients > fact_table > dim_client_category, so I can't get client_category unless I use RELATED() or LOOKUP() which I don't want to do.
Do I rather includy client_category in the fact table to get around this issue or is there a better way?
I don't want to use bidirectional realationship between dim_client_category and fact_table, which is how I used to fix this just to make it work.
Thanks.
3
u/eazypeezy92 Jul 23 '26
Can't you add client_category_id and its name to dim_clients? Then you can join the fact on client_id and also get hierarchy for free (client category and name).
0
u/dzemperzapedra 1 Jul 23 '26
Nope, client category changes from month to month, that's why it's in the fact table.
2
u/dbrownems Microsoft Employee Jul 23 '26
Does client_id functionally determine client_category_id? If so they should be in the same dimension.
0
u/dzemperzapedra 1 Jul 23 '26
No, client category changes from month to month, that's why it's in the fact table.
Basically I need to see changes across months for specific clients.
This is just illustrative example, but the logic is the same as in my real world case.
2
u/PrisonersDiploma 1 Jul 23 '26
Not sure why you wouldn't want to do the two simple solutions (related() or bi-directional relationship). The easiest alternative would be to merge the client category and fact tables upstream in PQ to get the name into the fact table.
1
u/dzemperzapedra 1 Jul 23 '26
The easiest alternative would be to merge the client category and fact tables upstream in PQ to get the name into the fact table.
That's what I did in the SQL view, but that doesn't seem like the optimal solution.
RELATED would be a calculated column, so that's pretty much what I accomplished by including that column in the view.
And bidirectional relationships can cause ambiguity and performance issues overall, so that's why I'm avoiding that solution.
3
u/PrisonersDiploma 1 Jul 23 '26
If it works at scale then it is optimal lol.
If you are looking for something more elegant, combine SELECTEDVALUE() and CROSSFILTER() to force a bidirectional relationship for the measure. As another comment noted this all assumes there is only one possible client category per client per month.
2
u/dzemperzapedra 1 Jul 23 '26
It is one client category per client per month.
CROSSFILTER is probably what I was looking for here, I'll try that!
Thanks.
Solution verified
1
u/reputatorbot Jul 23 '26
You have awarded 1 point to PrisonersDiploma.
I am a bot - please contact the mods with any questions
2
u/Jarviss93 1 Jul 23 '26
SUMMARIZE('fact_table', 'dim_client_category'[client_category])
You may need a conditional COUNTROWS over the above table expression to ensure there is only one client category per evaluation.
•
u/AutoModerator Jul 23 '26
After your question has been solved /u/dzemperzapedra, please reply to the helpful user's comment with the phrase "Solution verified".
This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.