Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter Projects based on 2 criteria

I'm trying (and trying) to filter a list of Projects that are both Forks AND Knives Only (see Venn Diagram)                 Projects    Utensils    Release Proj A        Fork         ...
Need Help
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    So this is going to depend on a number of things but the general approach to this is what I call a Complex Selector. Basically, you create a measure that returns 1 or 0 based upon whatever complex logic you wish to implement. So, you might implement something like:

     

    Measure =

      VAR __Project = MAX('Table'[Project])

      VAR __Forks = COUNTROWS(FILTER(ALL('Table'),'Table'[Project] = __Project && [Utensils] = "Forks"))

      VAR __Knives = COUNTROWS(FILTER(ALL('Table'),'Table'[Project] = __Project && [Utensils] = "Knives"))

    RETURN

      IF(__Forks > 0 && __Knives > 0, 1, 0)

     

    You can then use this in your Filters pane to filter your visualization for example.  

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

You could create a independent table at first.

Table 2 =
 DISTINCT('Table'[Projects])

Then create a measure to check if results matched.

Measure =
VAR a =
    VALUES ( 'Table 2'[Projects] )
VAR b =
    CALCULATETABLE (
        VALUES ( 'Table'[Utensils] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Projects] IN a )
    )
RETURN
    IF ( COUNTROWS ( EXCEPT ( B, VALUES ( 'Table'[Utensils] ) ) ) > 0, 0, 1 )

Then add this measure into filter pane of your slicer and set it as 1.

Here is my test file for your reference.