Urgent : Filter row in matrix with measure value

[Anonymous]

Hello everyone,

Is it possible to filter elements of rows in matrix using a measure?

To explain : 

I have a "Smart parent entity" measure which returns the entity to be displayed according to the filters selected. when I select 

I would like to display the values ​​for the selected entity and its children (entite_parent = smart_entite_parent)

I have 2 tables : 

Table arbo 

and table : "table donnees"

region, departement and pays are calculated columns. they are used to create "smart entite parent"

SMART_ENTITE_PARENT =
IF (
    AND (
        NOT ( HASONEVALUE ( Arbo[Lib_niv_2] ) ),
        NOT ( HASONEVALUE ( Arbo[Lib_niv_1] ) )
    ),
    FIRSTNONBLANK ( Table_donnees[pays], Table_donnees[pays] ),
    IF (
        AND ( HASONEVALUE ( Arbo[Lib_niv_2] ), NOT ( HASONEVALUE ( Arbo[Lib_niv_1] ) ) ),
        FIRSTNONBLANK ( Table_donnees[region], Table_donnees[region] ),
        FIRSTNONBLANK ( Table_donnees[Departement], Table_donnees[Departement] )
    )
)

I am forced to disable filtering between segments and matrix. in the following case I only want to display the France group :

Here I would only to see Groupe Mayenne :

The measure in matrix to see value is :

New Measure =
IF (
NOT ISINSCOPE ( Table_donnees[LIB_ENTITE] ),
CALCULATE (
SUM ( Table_donnees[VALEUR] ),
ALL ( Table_donnees[ENTITE_PARENT] ),
TREATAS ( VALUES ( Table_donnees[ENTITE_PARENT] ), Table_donnees[LIB_ENTITE] )
),
SUM ( Table_donnees[VALEUR] )
)

How can I filter only row where Entite_Parent = Smart_Entite_Parent ?

Thanks You for your help !

[m3tr01d]

hello @Anonymous,

what is the reason for keeping all the levels of the hirearchy in the Fact table (table de données)?

Can't you just keep the data at the lowest level of the hirearchy?

It seems bad practice to have the data set up like that in my opinion

[Anonymous]

Hi @m3tr01d , 

Users need to have this information, so I need to keep it 😞

How would you have configured data ?

Thank you for your help

[Greg_Deckler]

@Anonymous Yes, you want what I call a Complex Selector. The Complex Selector - Microsoft Power BI Community

[Anonymous]

Hi @Greg_Deckler Thanks for your reply but I don't see how the complex selector can act on the names of my lines. Could you help me on this? thank you very much.

[Greg_Deckler]

@Anonymous In general, assuming a disconnected table for your slicer:

Complex Selector Measure =
  VAR __Group = SELECTEDVALUE('SlicerTable'[Column])
  VAR __CurrentGroup = MAX('Table'[Group])
RETURN
  IF(__Group=__CurrentGroup,1,0)

[Anonymous]

Hi @Greg_Deckler,

I tried this solution, but it doesn't work because SMART_ENTITE is a measure

I found the beginning of a pontential solution.

I modified the displayed dax measure :

New Measure =
VAR entite_parent = "GROUPE MAYENNE"
RETURN
IF (
NOT ISINSCOPE ( Table_donnees[LIB_ENTITE] ),
CALCULATE (
CALCULATE (
SUM ( Table_donnees[VALEUR] ),
FILTER ( Table_donnees, Table_donnees[LIB_ENTITE] = entite_parent )
),
ALL ( Table_donnees[ENTITE_PARENT] ),
TREATAS ( VALUES ( Table_donnees[ENTITE_PARENT] ), Table_donnees[LIB_ENTITE] )
),
CALCULATE (
SUM ( Table_donnees[VALEUR] ),
FILTER ( Table_donnees, Table_donnees[ENTITE_PARENT] = entite_parent )
)
)

and it shows me what I want to see for the "Groupe Mayenne"

But now I would like to change the group "Groupe Mayenne"to SMART_entite so that it varies. But it doesn't work : 

Do you know how I could do it?

Thanks

[Anonymous · Accepted solution]

I found solution : I modified Smart_entite_parent. I replaced Haveonevalue by haveonefilter

So 

SMART_ENTITE_PARENT = 

IF (

AND (

NOT ( HASONEFILTER ( Arbo[Lib_niv_2] ) ),

NOT ( HASONEFILTER ( Arbo[Lib_niv_1] ) )

),

"FRANCE",

IF (

AND ( HASONEFILTER ( Arbo[Lib_niv_2] ), NOT ( HASONEFILTER ( Arbo[Lib_niv_1] ) ) ),

SELECTEDVALUE ( Arbo[Lib_niv_2] ),

SELECTEDVALUE ( Arbo[Lib_niv_1] )

)

)

and

New_Measure =

VAR entite_parent = [SMART_ENTITE_PARENT]

RETURN

IF (

NOT ISINSCOPE ( Table_donnees[LIB_ENTITE] ),

CALCULATE (

CALCULATE (

SUM ( Table_donnees[VALEUR] ),

FILTER ( Table_donnees, Table_donnees[LIB_ENTITE] = entite_parent )

),

ALL ( Table_donnees[ENTITE_PARENT] ),

TREATAS ( VALUES ( Table_donnees[ENTITE_PARENT] ), Table_donnees[LIB_ENTITE] )

),

CALCULATE (

SUM ( Table_donnees[VALEUR] ),

FILTER ( Table_donnees, Table_donnees[ENTITE_PARENT] = entite_parent )

)

)

Thanks @Greg_Deckler  for your help