Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Value fetching in hierarchy type data

Dear Super Heroes,

Below is my sample data.

SankeyThakkar_7_0-1648622706225.png


I need Output like below,

SankeyThakkar_7_1-1648623165708.png

 

I created below measure, Which is working only when level 4 is there. If level 4 is not exist it will not showing my remaining values,

 

Below is my measure,

Measure =
IF(
MAX('Table'[Level4]) <> "",
IF(
ISINSCOPE('Table'[Level4]),
CALCULATE(SUM('Table'[Value])),
IF(
ISINSCOPE('Table'[Level3]),
CALCULATE(SUM('Table'[Value]),'Table'[Level4]=""),
IF(
ISINSCOPE('Table'[Level2]),
CALCULATE(SUM('Table'[Value]),'Table'[Level3]=""),
IF(
ISINSCOPE('Table'[Level1]),
CALCULATE(SUM('Table'[Value]),'Table'[Level2]=""),
BLANK()
)))))


After using measure below is my output coming,

SankeyThakkar_7_2-1648623365590.png

Kindly help please.





4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous ,

Here are the steps you can follow:

1. According to the selected column – Transform – Unpivot Columns.

vyangliumsft_0-1649072959611.png

Result:

vyangliumsft_1-1649072959613.png

2. Create measure.

Measure =
var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev3")),_a)
Measure 3 =
var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev1")),_a)
Measure 4 = var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev2")),_a)
Measure 5 = var _a = CALCULATE(MAX('Table'[Value.1]),FILTER('Table',NOT(ISBLANK([Value.1]))))
return
if(_a in CALCULATETABLE(VALUES('Table'[Value.1]),FILTER(ALL('Table'),[Attribute]="Lev4")),_a)
For reference =
calculate(MAX('Table'[Attribute]),FILTER('Table',NOT(ISBLANK([Value.1]))))

3. Result:

vyangliumsft_2-1649072959613.png

Please click here for the pbix file

 

Best Regards,

Liu Yang

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

Anonymous
Not applicable

Thanks for help. I loose hope as no one is replying.

This solution has 2 major issues sir,

(1) Need to show in matrix so heirarchy is required.(ig level 4 iteam J is coming under what level of 1 ,2 and 3.
(2) If i applied this solution it increases row size very much.(if i have 500 rows as normal data set it will become 4500) So my actual data size is too much.

Anonymous
Not applicable

Anonymous
Not applicable

No solution?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors