topic Re: Value fetching in hierarchy type data in DAX Commands and Tips

Value fetching in hierarchy type data

Anonymous — Wed, 30 Mar 2022 06:58:51 GMT

Dear Super Heroes,

Below is my sample data.

I need Output like below,

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,

Kindly help please.

Re: Value fetching in hierarchy type data

Anonymous — Wed, 30 Mar 2022 07:01:49 GMT

tamerj1 amitchandak Jihwan_Kim Whitewater100 johnt75 Kindly help heroes

Re: Value fetching in hierarchy type data

Anonymous — Wed, 30 Mar 2022 10:22:34 GMT

No solution?

Re: Value fetching in hierarchy type data

Anonymous — Mon, 04 Apr 2022 11:50:35 GMT

Hi Anonymous ,

Here are the steps you can follow：

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

Result：

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:

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

Re: Value fetching in hierarchy type data

Anonymous — Tue, 05 Apr 2022 04:03:07 GMT

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.