cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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


JVIR
Frequent Visitor

Hello

Thanks. I found another way to work it out.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!