Forum Discussion

Milozebre's avatar
Milozebre
Helper V
9 years ago
Solved

Filter measure with mutiple table

Hello,

 

I created a measure with a filter on a table.

However, I would like to add another filter from another table.

Here is my measurement and I would like to add another filter in the measurement.

 

CALCULATE(DISTINCTCOUNT('Dossiers Traités/Equipe'[NumeroDossier]);FILTER('Dossiers Traités/Equipe';'Dossiers Traités/Equipe'[EquTRAIT] = "Voiture"  || 'Dossiers Traités/Equipe'[EquTrait] = "Moto" || 'Dossiers Traités/Equipe'[EquTrait] = "Velo" || 'Dossiers Traités/Equipe'[EquTrait] = "Bateau"))

 

I want add this filter : 

Filter('Dossiers Clos'; 'Dossiers Clos'[Libelle] = "Voiture"

 

I tried, related table, if, ... But I still have errors in the formulas

why add a filter to this extent and not a simple filter on the visualization ? The reason is simple, thaks to these measures, I will be able to make other calculations.

 

Thank you

 

Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    CALCULATE can use implict filters

     

    CALCULATE( Expression, [Filter 1]; [Filter 2]; ... )

     

    So remove the explicit FILTER function:

     

    MeasureName =
CALCULATE (
    DISTINCTCOUNT ( 'Dossiers Traités-Equipe'[NumeroDossier] ),
    OR (
        'Dossiers Traités-Equipe'[EquTRAIT] = "Voiture",
        OR (
            'Dossiers Traités-Equipe'[EquTrait] = "Moto",
            OR (
                'Dossiers Traités-Equipe'[EquTrait] = "Velo",
                'Dossiers Traités-Equipe'[EquTrait] = "Bateau"
            )
        )
    )
)

     

    And you can add the new filter as another parameter (each parameter acts with an implicit AND)

     

    MeasureName =
CALCULATE (
    DISTINCTCOUNT ( 'Dossiers Traités/Equipe'[NumeroDossier] );
    OR (
        'Dossiers Traités/Equipe'[EquTRAIT] = "Voiture";
        OR (
            'Dossiers Traités/Equipe'[EquTrait] = "Moto";
            OR (
                'Dossiers Traités/Equipe'[EquTrait] = "Velo";
                'Dossiers Traités/Equipe'[EquTrait] = "Bateau"
            )
        )
    );
  'Dossiers Clos'[Libelle] = "Voiture")

     

    Hope this helps

    David

3 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Community Champion
    9 years ago

    CALCULATE can use implict filters

     

    CALCULATE( Expression, [Filter 1]; [Filter 2]; ... )

     

    So remove the explicit FILTER function:

     

    MeasureName =
CALCULATE (
    DISTINCTCOUNT ( 'Dossiers Traités-Equipe'[NumeroDossier] ),
    OR (
        'Dossiers Traités-Equipe'[EquTRAIT] = "Voiture",
        OR (
            'Dossiers Traités-Equipe'[EquTrait] = "Moto",
            OR (
                'Dossiers Traités-Equipe'[EquTrait] = "Velo",
                'Dossiers Traités-Equipe'[EquTrait] = "Bateau"
            )
        )
    )
)

     

    And you can add the new filter as another parameter (each parameter acts with an implicit AND)

     

    MeasureName =
CALCULATE (
    DISTINCTCOUNT ( 'Dossiers Traités/Equipe'[NumeroDossier] );
    OR (
        'Dossiers Traités/Equipe'[EquTRAIT] = "Voiture";
        OR (
            'Dossiers Traités/Equipe'[EquTrait] = "Moto";
            OR (
                'Dossiers Traités/Equipe'[EquTrait] = "Velo";
                'Dossiers Traités/Equipe'[EquTrait] = "Bateau"
            )
        )
    );
  'Dossiers Clos'[Libelle] = "Voiture")

     

    Hope this helps

    David

    • Milozebre's avatar
      Milozebre
      Helper V
      9 years ago

      Sorry for the late response but with jet lag ... :)

      Hello,

       

      I just tested the formula it works.

      I didn't know that we didn'tt have to filter with Calculate.

       

      Thanks for the information and the solution.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    Can you post some sample data so that this issue can be recreated?