Got the following conundrum where I can see the technical problem, but no idea how to fix without breaking the model relationship integrity.
The mock up shows sales for multiple departments. Each dept has a manager and in some cases a manager is responsible for multiple depts. (marked up in the Manager column in the dept table). But we also have other managers that are measured on sales too. They may not be heading the dept but act in some form of support and should be measured on dept sales.
With a Manager table it is straight forward to link the fact table by inserting a related column from Dept[Manager]. Then with some calculated columns generate the hierarchical results and with a separate measure [Mgr Sales] work out the sales by manager. No need to sum this matrix because the total would have no meaning due to double counting within the manager hierarchy. All of this works fine.
The issue centres around Manager 7. Manager 7 is shown in the [Manager2] column of depts and is in the manager table with his reporting line.
How do we include his sales in the sales by manager table without disturbing the existing relationships which in my view are all fine.
My current workaround is to work Manager 7 depts separately in a Switch statement, but this is far from efficient and needs changing whenever such a manager leaves and a new one joins. The users really just want to populate the Manager 2 column in the depts table and let DAX work its magic.
PS - Now that I’m semi-retired, I’m working through the accelerator stuff. Wouldn’t classify myself as a newbie, but you always get a fresh nugget of knowledge even recapping the basics. This weeks nugget was the extended date table. Once I’ve caught up with the older ones, it would be cool to come into the chat on the current ones.
Thanks @sam.mckay and @BrianJ for hosting these.
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!
Bring Manager2 into the fact table as a related column
Create inactive relationship between Manager2 (in fact table) and manager table.
Work a second variable called result2 into the logic with USERRELATIONSHIP.
Return result + result2
I haven’t tried this (hoped there was a better solution out there) because:
Additional Column in fact table will take up additional space on a large dataset.
(although there would be a lot of blanks because manager2 isn’t relevant on every dept)
I’ll be doubling up the work done by the formula engine - 2 calcs added together and probably 2 sweeps thru to do the SE stuff.
So whilst I have a workaround, I’m not sure this would be optimal on a large fact table.
Thanks for giving it a whirl Brian. What I proposed in the earlier post does work but you might have a different approach.
I just put it into the company’s model and all pretty fine from speed point of view.
Just seems wrong to addition in DAX - that means lower performance speeds.
A possible workout is making two manager groups in the lookup table “Depts” and add the sales of the two groups together with an extended DAX formula, based upon your “Mgr Sales” measure.
With this method it is possible to make even more manager groups, it is important to determine how the sales amounts of the different managers should be grouped together.
In my opinion, it is sufficient to connect the “Managers” table only to the “Depts” table, no need to connect it to the fact table Sales.
In the Sales fact table, there is no need for the related “Sales manager” column, nor for “Department Name” (Depts ID is sufficient) , both deleted out of the Sales table.
Added a virtual second connection from table “Depts” to table “Managers”, to be able to connect the second manager group
Instead of connecting “Depts” and “Managers” on a manager name, it would be advisable to use a managerID
Added in table “Managers” a column “Managergroup” to distinguish Managergroup I and II, only for better understanding of the used two groups. And a “Hierarchy Group 2” for possible grouping of managers in Group 2 is created with a preliminary setup, but this needs to be reviewed to meet the user requirements.
Adjusted the measure “Mgr Sales” , which allows adding the sales of two manager-groups together in a report. The second group is calculated with the help of the function “USERRELATIONSHIP”, currently setup / exists of only the sales of (the departments of) manager 7.
Mgr Sales =
Var SelectedManagerId = SELECTEDVALUE(Managers[ManagerId])
Var Managergroup1 = CALCULATE([Tot Sales], // first manager group, as given from the forum request, see Table Depts
FILTER(ALL(Managers),
PATHCONTAINS(Managers[MgrHierarchy Group 1],SelectedManagerId)))
Var Managergroup2 = CALCULATE([Tot Sales], // second manager group, currently only existing of Manager 7, see / registered in Table Depts
FILTER(ALL(Managers),
PATHCONTAINS(Managers[Hierarchy Group 2], SelectedManagerId)),
USERELATIONSHIP( Depts[Manager Group2], Managers[Manager]))
Return
Managergroup1 + Managergroup2
A possible report looks as follows: (whereby sales of manager 7 is not added to another manager)
I hope this solution meets your requirements.
If you have further questions or remarks, please let me know !
Kind regards, DS
PS Thank you for letting me know about the possibilities of the “path”-function, I was not aware of it !
MgrHierarchy Group 1 = path(Managers[ManagerId],Managers[ReportsTo])
I can see that working. No where near my production model at moment but will give a go. I had moved it on without the PathContains piece and with UserRelationship but kept the related columns in the fact table to maintain star schema.
I will give your fix a go in my production model (2m + rows in fact table) and compare to mine for speed. If it breaches the star schema for one measure and is faster then that’s the way to go.
Put this in play on our production model this afternoon. My solution was satisfactory for us but yours is more efficient even though it creates a snowflake.
So fact file is 4m rows (had that wrong in earlier post)
Manager count is around 40 with about 6 fitting into Manager2.
The production visual has multiple measures for actual, budget and PY with % variances and rankings against budget and LY, with rankings then summed to create a league.
Highlights:
Report refresh time moved from 1600 to 1700 ms on my version to 1400 to 1500 ms with your solution.
Data Refresh time speeded up by about a minute, I guess because didn’t need to generate Manager and Manager2 in the fact table.
PBIX file size reduced by about 8%, probably for same reasons
Just goes to show - sometimes star schema can be breached just a little bit!
