Multiple Filter context and date

[greg0307]

Hi,

I'm having issue with filtering context and multiple date, below schema of database and DAX measure.

BP Ratio Clôturés dans les délais = 1 -

DIVIDE(

(CALCULATE(

DISTINCTCOUNT(CompteRendu[id_bp]),

CompteRendu[Retard]="Retard",ALL('Date'),CompteRendu[code_categorie]="P0") +

(CALCULATE(

DISTINCTCOUNT(CompteRendu[id_bp]),

CompteRendu[Retard]="Retard",CompteRendu[etat_bp]<>"CLO",ALL('Date'),CompteRendu[code_categorie]="P0") -

CALCULATE(

DISTINCTCOUNT(CompteRendu[id_bp]),

CompteRendu[Retard]="Retard",CompteRendu[etat_bp]<>"CLO",CompteRendu[code_categorie]="P0"))),

CALCULATE(

DISTINCTCOUNT(BP[id_bp]),BP[etat_bp]="CLO",REMOVEFILTERS(CompteRendu[Date Fin Réelle]),KEEPFILTERS('Date'[Date])))

Filter context must filter on "date fin réelle" table "Compte-Rendu" and "date début prévu" on "BP" for measure "BP Ratio Clôturés dans les délais".

Value on three table on left is ok, result of measure are wrong, after many days of trying to resolve this issue.

Thanks for your help

[v-janeyg-msft]

Hi, @greg0307 

According to your description, the result of the measure is wrong,right? But you didn't provide the code for the measure. The contexts in the card and table are not the same, so you need to write the corresponding code. I think the code and screenshots you provided are not helpful in solving your problem.

If you hasn't resolved the issue, please share some sample fake data and your desired result like,and feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[greg0307]

Hi, 

Already try this context filtering appying too.

[jdbuchanan71 · Accepted solution]

@greg0307 

Perhaps try creating your inner calculations as discrete measures then using them in your final calculation.  This can help with trouble shooting the intermediate steps.

A = CALCULATE (
    DISTINCTCOUNT ( CompteRendu[id_bp] ),
    CompteRendu[Retard] = "Retard",
    ALL ( 'Date' ),
    CompteRendu[code_categorie] = "P0" )

B = CALCULATE (
    DISTINCTCOUNT ( CompteRendu[id_bp] ),
    CompteRendu[Retard] = "Retard",
    CompteRendu[etat_bp] <> "CLO",
    ALL ( 'Date' ),
    CompteRendu[code_categorie] = "P0" )

C = CALCULATE (
    DISTINCTCOUNT ( CompteRendu[id_bp] ),
    CompteRendu[Retard] = "Retard",
    CompteRendu[etat_bp] <> "CLO",
    CompteRendu[code_categorie] = "P0" )

D = CALCULATE (
    DISTINCTCOUNT ( BP[id_bp] ),
    BP[etat_bp] = "CLO",
    REMOVEFILTERS ( CompteRendu[Date Fin Réelle] ),
    KEEPFILTERS ( 'Date'[Date] ) )

BP Ratio Clôturés dans les délais =
1 - DIVIDE ( ( [A] + ( [B] - [C] ) ), [D] )

[greg0307]

It's ok, just don't forget to apply correct context filter on each measure (Date Slicer).

Thanks for you help, too many time spent for simply things 😞