Hide Hierachy duplication in Powerpivot (Row Labels)

Question

I am reporting on performance of legal cases, from a SQL database of activities. I have a main table of cases, which has a parent/child hierarchy. I am looking for a way to appropriately report on case performance, reporting only once for a parent/child group (`Family').

An example of relevant tables is:

Cases

ID  |  Client   | MatterName | ClaimAmount | ParentID | NumberOfChildren |
 1  | Mr. Smith | ABC Ltd    | $40,000     |       0  |       2          |
 2  | Mr. Smith | Jakob R    | $40,000     |       1  |       0          |
 3  | Mr. Smith | Jenny R    | $40,000     |       1  |       0          |
 4  | Mrs Bow   | JQ Public  |  $7,000     |       0  |       0          |

Payments

ID  |  MatterID | DateReceived  | Amount |
 1  |      1    |    14/7/15    |   $50  |
 2  |      3    |    21/7/15    |  $100  |

I'd like to be able to report back on a consolidated view that only shows the parent matter, with total received (and a lot of other similar related fact tables) - e.g.

Client   | MatterName | ClaimAmount | TotalReceived |
Mr Smith | ABC Ltd    | $40,000     | $150          |
Mrs Bow  | JQ Public  |  $7,000     |   $0          |

A key problem I'm having is hiding row labels for irrelevant rows (child matters). I believe I need to

Determine whether the current row is a parent group
Consolidate all measures for that parent group
Filter on that being True? Place all measures inside IF checks?

Any help appreciated

greggyb greggyb · Accepted Answer · 2015-10-01T18:26:38

How many levels does your hierarchy have? If it's just 2 levels (parents have children, children cannot be parents), then denormalize your model. You can add a single column for ParentMatterName and use that as the rowfilter in pivots. If there is a reasonable maximum number of levels in your data (we typically look at <=6 as reasonable) then denormalization is probably preferable, and certainly simpler/more performant, than trying to dynamically roll up the child measure values.

Edits to address comment below:

Denormalizing your data structure in this case just means going to the following table structure:

Cases
ID | Client    | ParentMatterName | MatterName | ClaimAmount
1  | Mr. Smith | ABC Ltd          | ABC Ltd    | $40,000
2  | Mr. Smith | Jakob R          | ABC Ltd    |      $0
3  | Mr. Smith | Jenny R          | ABC Ltd    |      $0
4  | Mrs Bow   | JQ Public        | JQ Public  |  $7,000

Regarding nomenclature - Excel is stupid, and so is DAX. Here is the way to think about these things to help minimize confusion - these are important concepts as you move forward in more complex DAX measures and queries.

Here are some absolutely truthful and accurate statements to show how stupid the nomenclature can get:

FILTER() is a table
Pivot table rows are filter context
FILTER() applies additional filter context when used as an argument to CALCULATE()
FILTER() creates row context internally which to evaluate expressions
FILTER()'s arguments are affected by filter context from pivot table rows
FILTER()'s second argument evaluates an expression evaluated in the pivot table's rowfilter context in the row context of each row in the table in the first argument

And so on. Don't think of a pivot table as anything but filters. You have filters, slicers, rowfilters, columnfilters. Everything in a pivot table is filter context.

Links:

Denormalization in Power Pivot

Denormalizing Dimensions

Hide Hierachy duplication in Powerpivot (Row Labels)

1 Answers