Forum Discussion

josepcervello's avatar
josepcervello
Frequent Visitor
7 years ago

Parent aggregation getting last child value

Hi,    I created a Pivot table based on Switch formula and measures that are applied depending on the "level1-level2" name.   Why paret aggregations are just getting the last value of the childs?...
Need Help
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

Hi josepcervello 

Sorry my bad, SELECTEDVALUE replaces your MAX's in the SWITCH as below.

 

Measure 2 = 
VAR h2 = SELECTEDVALUE(YourTable[TIS-Level2])
VAR h1 = SELECTEDVALUE(YourTable[TIS-Level1])
VAR hh = IF(ISBLANK(h2), h1, h1 & " - " & h2) 
VAR py = [POPS BTS Previous Year] + [POPS Decommissioning Previous Year] -- + all other Previous Year Measures
VAR cy = [POPS BIS Current Year] + -- + all current year measures
RETURN 
SWITCH (
    hh,
    "POPS Previous Year", py,
    "POPS Current Year", cy,
    "POPS Previous Year - POPS POPS BTS Previous Year", [POPS BTS Previous Year]
    -- Rest of the rules  
)

 

Regards,
Mariusz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

josepcervello's avatar
josepcervello
Frequent Visitor
7 years ago

Based on your code, I should add to my hierarchy table two rows with a blank level 2, as I show in the image. If not, the code is not working...


  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi josepcervello,

    It should work withot adding extra lines.

    VAR h2 = SELECTEDVALUE(YourTable[TIS-Level2])
VAR h1 = SELECTEDVALUE(YourTable[TIS-Level1])
VAR hh = IF(ISBLANK(h2), h1, h1 & " - " & h2)  -- this checks for level 2 selection, when you are in your table on a total level h2 is blank you can see only h1


    Thanks Mariusz
    Regards,
    Mariusz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.