Forum Discussion

smerchantkitab's avatar
smerchantkitab
Frequent Visitor
6 years ago
Solved

Dynamic Measure based on the selected filter - Hierarchical data

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

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    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.

     

9 Replies