Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thank you
I pass you all the info:
The table config:
The formula:
(every row is a sum measure calculated in a measures table)
The hierarchy is built as following:
Thank you very much in advance.
Hi @josepcervello
You need to add all the measures when on TIS-Level1 Something like below.
Measure 2 = VAR h2 = SELECTEDVALUE('dynamic column'[Index]) VAR h1 = SELECTEDVALUE('dynamic column'[Column 2]) 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.
What are these coulmns refering to? The hierarchy table?
'dynamic column'[Index] 'dynamic column'[Column 2]
Thank you,
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.
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...
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |