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.

     

AllisonKennedy's avatar
AllisonKennedy
Community Champion
6 years ago

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's avatar
    smerchantkitab
    Frequent Visitor
    6 years ago

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