Forum Discussion

Sibrulotte's avatar
Sibrulotte
Icon for Helper IV rankHelper IV
1 year ago
Solved

Remove filter nor removing filters

Hi,  I've joing a basic model that ressembles something that work great for a topic we have.    I'd like to show the number of customers that were present on may 31st of the previous fiscal year o...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Sibrulotte ,

     

    I update the measure and you can try this.

    NB_epargnant_31_mai_comp =
VAR vdern_date_31_mai_prec =
    DATE ( MAX ( Calendrier[Fiscal year] ), 05, 31 ) - 365
VAR result =
    CALCULATE (
        DISTINCTCOUNT ( Soldes[Epargnant_ID] ),
        FILTER (
            ALLEXCEPT ( Soldes, Comptes_epargnants, 'Régimes' ),
            Soldes[Date_solde] = vdern_date_31_mai_prec
        )
    )
RETURN
    result

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago
the number of customers that were present on may 31st of the previous fiscal year of

what does "present"  mean?  They had saldos?

 

 

 

Sibrulotte's avatar
Sibrulotte
Icon for Helper IV rankHelper IV
1 year ago

Wow, you really did me dirty exposing my fake client names lol.

Yes, it's a count of Epargnant_ID. and Epargnant can have multiple accounts, but even if this model doesn't represent that reality, yes it's still a distinct customer count.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Sibrulotte ,

     

    Please refer to below code.

    NB_epargnant_31_mai_comp = 
var vdern_date_31_mai_prec = date(max(Calendrier[Fiscal year]), 05,31)-365
var result = calculate(distinctcount(Soldes[Epargnant_ID]), FILTER(ALL(Soldes),Soldes[Date_solde] = vdern_date_31_mai_prec))
var result1 = calculate(distinctcount(Soldes[Epargnant_ID]), REMOVEFILTERS(Soldes),Soldes[Date_solde] = vdern_date_31_mai_prec)
return
result1

    If you want to use REMOVEFILTERS() you can try result1. You can also try ALL() function like result, the result is the same.

     

    Best Regards,
    Rico Zhou

     

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

     

    • Sibrulotte's avatar
      Sibrulotte
      Icon for Helper IV rankHelper IV
      1 year ago

      Hi Zhou, thank you for the response. The result works partially since the measure does not provide a ventilated result when using in a matrix with a reference table like "Régime" in this file. All values of régime come out with 6, but I was expecting 3 and 3.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Sibrulotte ,

         

        I update the measure and you can try this.

        NB_epargnant_31_mai_comp =
VAR vdern_date_31_mai_prec =
    DATE ( MAX ( Calendrier[Fiscal year] ), 05, 31 ) - 365
VAR result =
    CALCULATE (
        DISTINCTCOUNT ( Soldes[Epargnant_ID] ),
        FILTER (
            ALLEXCEPT ( Soldes, Comptes_epargnants, 'Régimes' ),
            Soldes[Date_solde] = vdern_date_31_mai_prec
        )
    )
RETURN
    result

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

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