Show different measures with Hierarchie filtered with combo chart

[Cobra77]

Hi 

we want to see on the graph 2 measures with classical hierarchy (no problem) , (mes1 and mes 2 )

And another measure (mes3)  that appears only when it's level without taking the kids

sample hierarchy :

sample1 : if i filter on Level3 with value "TY" , i want for mes3 only data with id on 5 (FK), no children ( with id 7,8,9,10,11)

sample2 : if i filter on Level1 with value "CC" , i want for mes3 only data with id on 1 (FK), no children ( with all id )

thanks for your helps

[v-juanli-msft]

Hi @Cobra77

Please let me know if the result of your sample should be shown as below

Sample1

Sample2

---

we want to see on the graph 2 measures with classical hierarchy (no problem) , (mes1 and mes 2 )

And another measure (mes3)  that appears only when it's level without taking the kids

---

Where is your mes1 and mes 2 , are the columns on the picture columns not any measure you wanted?

Best Regards

Maggie

[Cobra77]

Hi @Maggie,

Yes for the mes3 this is the expected result

But for the mes1 and mes2 we want the children (it's ok for these measures)

the report can be filtered on different levels, on different columns of the hierarchy

Thanks,

Best Regards

[v-juanli-msft]

Hi @Cobra77

1.Enter data to create a new table

2.create a measure and add this measure to the Visual filter, and apply a filter in this measure to show when it is equal to 1.

Measure =
VAR myselection =
    SELECTEDVALUE ( Table1[selection], "selection" )
RETURN
    SWITCH (
        TRUE (),
        myselection = "level1", IF (
            MAX ( [Level2] ) = "NULL"
                && MAX ( [Level3] ) = "NULL"
                && MAX ( [Level4] ) = "NULL",
            1
        ),
        myselection = "level2", IF ( MAX ( [Level3] ) = "NULL" && MAX ( [Level4] ) = "NULL", 1 ),
        myselection = "level3", IF ( MAX ( [Level4] ) = "NULL", 1 ),
        1
    )

3.add the column Table1[selection], “level1”, “level2”, ”level3” to the slicer

Reference:

Using a Slicer to show different measures

Best Regards

Maggie

[Cobra77 · Accepted solution]

Hi @Maggie

by taking inspiration from your idea of a parameter table , its ok with this measure :

mes3 :=

VAR lvl = IF(HASONEVALUE('98 - Axe filter'[Level_flt]) && HASONEFILTER('98 - Axe filter'[Level_flt]); VALUES('98 - Axe filtre'[Level_flt]) ; -1)

VAR filt = FILTER('02 - Axe';'02 - Axe'[Level] = lvl )

RETURN IF ( lvl >= 0 &&
SWITCH(lvl ; 0 ; HASONEFILTER('02 - Axe'[level0] ) ;
1 ; HASONEFILTER('02 - Axe'[level1] ) ;
2 ; HASONEFILTER('02 - Axe'[level2]) ;
3 ; HASONEFILTER('02 - Axe'[level3]) ;
4 ; HASONEFILTER('02 - Axe'[level4]) ;
5 ; HASONEFILTER('02 - Axe'[level5]) ;
6 ; HASONEFILTER('02 - Axe'[level6]) ;
7 ; HASONEFILTER('02 - Axe'[level7]) ;
8 ; HASONEFILTER('02 - Axe'[level8])) ;
CALCULATE([NB] ; filt) ; BLANK())

thanks