Deactivate filter in measure but keep selectedValue to create new filter

[Anonymous]

Hello,

I need your help. I have a table with a notion of parent and child (Region / Department / City). When we select a parent entity, I would like to display a table with the detail of the indicators for the child entities but also the values ​​for the parent entity. For child entities, it works well but not for parents.

Here is what I did but it returns an empty value:

Thank's for your help

[PaulOlding · Accepted solution]

Hi @Anonymous ,

I'm not totally sure this is what you're after.  Hopefully I've interpreted your question correctly.

It looks to me like in your sample data the total of VALEUR for the children don't add up to the VALEUR for the parent.  I guess that's the issue you're trying to solve?  Just using SUM(NEW_TABLE[VALEUR]) in the matrix would result in the sum of the children's value being displayed for the parent.

Here's a measure to give the parent's amount for parent rows and children's amount for children.

New Measure =

IF(

NOT ISINSCOPE(NEW_TABLE[LIB_ENTITE]),

CALCULATE(

SUM(NEW_TABLE[VALEUR]),

ALL(NEW_TABLE[ENTITE_PARENT]),

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

),

SUM(NEW_TABLE[VALEUR])

)

Here's the results of your sample data, with a comparison against a simple SUM of VALUER.

[Anonymous]

Hi @PaulOlding 

Thank's so much it seems to work 🙂

However, I will need to use this measure to create several measures. (I want to create metrics for some values ​​in the co_indic column) but my new metric doesn't seem to work.

Measure = CALCULATE([New Measure],FILTER(NEW_TABLE,NEW_TABLE[CO_INDIC]="ACT0860"))

I want to have a table like this but I want to put the names of the indicators in place of their codes and not display the Total row (last row of the table).

Thanks

[PaulOlding]

@Anonymous 

Can't you put CO_INDIC in the Column field well of a matrix visual?  No need to have separate measures then.

You mention wanting a description rather than the code.  If you don't have that in your model you could add it as an extra table.  A table with 2 columns: CO_INDIC, DESCRIPTION that has one row per CO_INDIC.  Create a relationship between this and NEW_TABLE on CO_INDIC.

If you really want to have searate measures then try this syntax:

ACT0850 =

CALCULATE(

[New Measure],

NEW_TABLE[CO_INDIC] = "ACT0850"

)

[Anonymous]

@PaulOlding Thank's you so much ! 🙂

It's perfect !

Have a Nice day 🙂