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

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

Thanks

AllisonKennedy's avatar
AllisonKennedy
Community Champion
6 years ago

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'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