Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
JVIR
Frequent Visitor

Countrows inside calculate with filters

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

2 REPLIES 2
mahoneypat
Microsoft Employee
Microsoft Employee

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 ( FirstValueSecondValue )

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Hello

Thanks. I found another way to work it out.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.