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.  

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Here is a way to do this (if I understood correctly).  First create a separate DAX table for your slicer with:

SlicerProjects = Values(Projects[Projects])

 

Having this separate table with no relationship to your Projects table keeps it from filtering the table visual.

 

 

Then use this measure in a table visual with the Projects[Projects] columns:

 

Projects Same Utensils =
VAR selectedproject =
SELECTEDVALUE ( SlicerProjects[Projects] )
VAR selectedutensils =
CALCULATETABLE (
VALUES ( Projects[Utensils] ),
ALL ( Projects ),
Projects[Projects] = selectedproject
)
VAR currentutensils =
VALUES ( Projects[Utensils] )
VAR sametables =
AND (
ISBLANK ( EXCEPT ( selectedutensils, currentutensils ) ),
ISBLANK ( EXCEPT ( currentutensils, selectedutensils ) )
)
RETURN
IF ( sametables = TRUE (), 1, BLANK () )

 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat