hello folks,
I have a modeling quandary which I’m hoping to either resolve by turning the model into a true star schema or perhaps some cool/performant DAX – screenshot & PBIX files are attached.
Basically, we have a ‘GL Accounts’ dimension whose accounts that may belong to more than one GL Hierarchies (from the GL Hierarchy’ table) , so for example, ‘GL Account’ 1000000 belongs to ‘Gross Product Revenue’, ‘Income Statement’ as well as ‘Net Revenue’.
This table, as such, is involved in a one to many with the ‘Ledger’ fact, but also, one to many with the ‘GL Hierarchy’. I don’t really like this design, and obviously it creates inflated results.
On the other hand, if I can help it, I don’t want to inflate both ‘GL Accounts’ dimension by adding the ‘GL Hierarchy ID’ as well as the Ledger fact fable, by adding the same ‘GL Hierarchy ID’ column.
Is there a nifty solution or remodel, to achieve the desired result in the screenshot?
thank you kindly for any expert insight,
Cos
It’s just a filter context problem. You need to pass the filter from GL Hierarchy through GL Accounts to Ledger. This isn’t automatically done because filters propagate from the 1-side of a 1-to-many relationship to the many side. You are trying to go in the opposite direction by moving the filter from GL Hierarchy (the many side) to GL Accounts (the one side) then propagate the filter to Ledger in the expected 1 to many.
awesome, thanks so much, yes, that indeed worked.
just struggling a tad with this concept, the filter that removes any filter on ‘GL Accounts’ via (ALL), but then does a COUNTROWS > 0 – puzzled, much
This kind of thing is difficult to get your head around at first.
Think of it like this:
ALL('GL Accounts') clears any filter on the GL Accounts table, making sure that you’re starting with a clean slate.
COUNTROWS(RELATEDTABLE('GL Hierarchy')) > 0 then applies a new filter that only includes those GL Accounts with a corresponding entry in the GL Hierarchy table.
There’s loads of ways you can go about moving a filter like this. It’s a common problem.
Here’s probably a more transparent way to go about filtering Ledger based on GL Hierarchy:
Create two virtual tables containing the IDs from both sets (GL Accounts and GL Hierarchy) and then take the intersection:
NewAmount 2 =
VAR __GLHierarchy =
CALCULATETABLE(
VALUES('GL Hierarchy'[GL Account ID])
// could add other filters here if needed
)
VAR __Ledger =
CALCULATETABLE(
VALUES(Ledger[GL Account ID])
// could add other filters here if needed
)
VAR __intersect =
INTERSECT(
__GLHierarchy
, __Ledger
)
RETURN
CALCULATE(
SUM('Ledger'[Amt]),
'Ledger'[GL Account ID] IN __intersect
)
by the way, thank you for describing your problem and sharing enough for others in the community to pick it up and try to solve it. because you included a minimal working example, i didnt have to mock up a pbix file myself. that made helping easy. i probably wouldnt have replied if i would have had to create that myself
thank you so much, @HufferD. Even though I’m sure this concept was covered in one of the Italians’ courses I think I probably bought all of them, but did not truly master every point apparently – it is people like you whom I stand on the shoulders of, shoulders of giants – I am privileged and thankful to be a little consumer in the community, and to have you guys, part of the awesome support community. Kudos & many, many thanks for all responses and your kindness to respond so thorough.
Make the GL Hierarchy → GL Accounts relationship bidirectional.
Bidirectional relationships are best avoided when possible but they aren’t universally evil.
Use CROSSFILTER in your DAX to achieve the same effect. For example,
Probably the best option in the long run (assuming it’s possible) would be to change the granularity of the Ledger fact table to use the GL Hierarchy Node ID rather than the GL Account ID and then consolidate the two GL dimension tables into one.
