Countrows inside calculate with filters

[JVIR]

Hi,

I have a problem with the following measure. I need to implement the commented code. In fact, I'd like to also filter all the

Contrats_Ent[SiteNum] where the count is 0.
Here is my measure

_Churn MtNet =
VAR minDate =
    MIN ( 'Contrats_Date'[Date] )
VAR maxDate =
    MAX ( 'Contrats_Date'[Date] )
RETURN
    CALCULATE (
        SUM ( Contrats_Fact[MtNet] ) * -1,
        FILTER (
            Contrats_Ent,
             ( Contrats_Ent[ContDtResil] <= maxDate
                && Contrats_Ent[ContDtResil] >= minDate )
        ),
        FILTER (
            Contrats_Fact,
             ( Contrats_Fact[PromoDeb] <= maxDate
                && Contrats_Fact[PromoFin] >= maxDate )
        ) /*CALCULATE(
                COUNT(Contrats_Ent[SiteNum]),
                        FILTER (Contrats_Ent, ISBLANK(Contrats_Ent[ContDtResil]) ||
                            Contrats_Ent[ContDtResil] < minDate && Contrats_Ent[ContDtResil] > maxDate
                        )
        )   */
    )

Many thanks for your help.
Jerome

[mahoneypat]

Please try this expression.  Note that I modified your FILTER parts to use better practice (just filter the column(s) you need, not the whole table).

_Churn MtNet =
VAR minDate =
    MIN ( 'Contrats_Date'[Date] )
VAR maxDate =
    MAX ( 'Contrats_Date'[Date] )
VAR FirstValue =
    CALCULATE (
        SUM ( Contrats_Fact[MtNet] ) * -1,
        FILTER (
            ALL ( Contrats_Ent[ContDtResil] ),
            Contrats_Ent[ContDtResil] <= maxDate
                && Contrats_Ent[ContDtResil] >= minDate
        ),
        FILTER (
            ALL ( Contrats_Fact[PromoDeb] ),
            Contrats_Fact[PromoDeb] <= maxDate
                && Contrats_Fact[PromoFin] >= maxDate
        )
    )
VAR SecondValue =
    CALCULATE (
        COUNT ( Contrats_Ent[SiteNum] ),
        FILTER (
            ALL ( Contrats_Ent[ContDtResil] ),
            ISBLANK ( Contrats_Ent[ContDtResil] )
                || ( Contrats_Ent[ContDtResil] < minDate
                && Contrats_Ent[ContDtResil] > maxDate )
        )
    )
RETURN
    DIVIDE ( FirstValue, SecondValue )

Pat

[JVIR]

Hello

Thanks. I found another way to work it out.