Dynamic Measure based on the selected filter - Hierarchical data

[smerchantkitab]

I am trying to get a measure which dynamically return the value based on the selected level.  I have a dataset like the table below. So if A (1st row) is selected I should get 5 but if B/c is selected I shoud get 12.  This should all be in group by id column e.g. 100 or 101.  I have also have sorting order column which basically sort the Id column so I can maintain 100.0.0.0 in order.

id	h1	h2	h3	h4	number1	number2	number3	number4
100.0.0.0	A				5	0	0	0
100.1.0.0	A	a				0	0	0
100.2.0.0	A	b				4	0	0
100.3.0.0	A	c				1	0	0
101.0.0.0	B				23	0	0	0
101.1.0.0	B	a				1	0	0
101.2.0.0	B	b				4	0	0
101.3.0.0	B	c				12	0	0
101.4.0.0	B	d				1	0	0
101.5.0.0	B	e				5	0	0
101.6.0.0	B	f				0	0	0

Basically, this dataset is based on the book structure so 100 or 101 (id) is the chapter and each chapter has h1 to h4 headings  and corresponding numbers of heading.

Any guidance on how can achieve this?

Thanks

[amitchandak]

@smerchantkitab , This is an example how to change value based on the selected value

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[smerchantkitab]

@amitchandak this look promising but not sure what would be the individual measure would be based on max, first?

Thanks

[AllisonKennedy]

@smerchantkitab  I'm still not sure what your desired result is?

If your raw data looks as above, it won't matter if you use MAX or SUM or AVERAGE as you only have one value in the Number 1 column. If you only need this to work by ID and are selecting A or B, then I'm not sure what the problem is as they seem to have the same granularity? Please provide sample of what you need.

[smerchantkitab]

@AllisonKennedy 

Thanks.  Here is the dataset with desire results.  I don't know if you can see but based on the filter I am picking up the data from appropriate level column. so if A.3 is selected I should get h2_i_cont corresponding value.

Updated: @AllisonKennedy  Basically, I have four cards but I need a single measure which returns the value from the corresponding column for the selected level (slicer).   

[v-xuding-msft · Accepted solution]

Hi @smerchantkitab ,

I create a sample. Please have a try to check if it is what you want.

• Create a new table and use the columns as slicer.

Table 2 = SELECTCOLUMNS('Table',"h1",'Table'[h1],"h2",'Table'[h2])

•  Create a measure.

Measure = 
var h1 = CALCULATE(SUM('Table'[h1_i_cnt]),FILTER('Table','Table'[h1] = SELECTEDVALUE('Table 2'[h1])))
var h2 = CALCULATE(SUM('Table'[h2_i_cnt]),FILTER('Table','Table'[h2] = SELECTEDVALUE('Table 2'[h2])))
return
IF(HASONEVALUE('Table 2'[h2]),h2,IF(HASONEVALUE('Table 2'[h1]),h1,0))

For more details, please see the attachment.

[smerchantkitab]

I used another technique to resolve this issue.  I can create a calculated column based on the level so if 1.0.0.0. This is level one.  1.1.0.0 leve two etc then created a measure which gives me a figure based on the level selected.

Thanks

[AllisonKennedy]

Are you expecting 12 or 23 when B is selected? 

Can you provide sample tables of what data looks like raw and what your desired result is? 

[smerchantkitab]

@AllisonKennedy I have provided the raw data above.  If B is selected I should get 23.

[lbendlin]

Have you tried testing for ISBLANK(h4) ? You can use that to switch computations.

IF(ISBLANK(h4),h2_count,h1_count)