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 selecte...
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.

     

smerchantkitab's avatar
smerchantkitab
Frequent Visitor
6 years ago

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's avatar
v-xuding-msft
Community Support
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.

 

  • smerchantkitab's avatar
    smerchantkitab
    Frequent Visitor
    6 years ago

    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