Forum Discussion

FPackermanGTA's avatar
FPackermanGTA
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Filter two tables from one slicer using OR logic

Hello,   Let me simplify the problem I'm trying to solve. I have two tables, one has computers from Active Directory and the other has User data from HR so,   The first table has the Computer Nam...
General Comment
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Apologies...you need the INTERSECT function. As in

     

    Create the measure :

    Or filter =
COUNTROWS ( INTERSECT ( VALUES ( 'Ind OU'[OU] ), VALUES ( Computer[OU] ) ) )
    + COUNTROWS (
        INTERSECT ( VALUES ( 'Ind Users'[Username] ), VALUES ( Computer[Username] ) )
    )

    Add the [Or filter] to the filter for the visual in the filter pane and set the value to greater or equal to 1:

    I've attached a sample PBIX file

PaulDBrown's avatar
PaulDBrown
Icon for Community Champion rankCommunity Champion
4 years ago

Maybe...

Create a copy of the user table using 

Ind User = 'User'

and another for OU using

Ind OU = DISTINCT (Computer [OU])

 

Leave these tables unrelated. 
Create a measure along the lines of:

Row filter =

COUNTROWS(TREATAS(VALUES ( [Ind User [Username]), Computer[Usernme))

+
COUNTROWS(TREATAS(VALUES(Ind OU [Computer [OU]), Computer [OU]))

 

now add this [Row filter] measure to the filters for the visual in the filter pane and set the value to greater or equal to 1 and use the fields from the unrelated tables in the slicers

FPackermanGTA's avatar
FPackermanGTA
Icon for Advocate I rankAdvocate I
4 years ago

Thanks for the reply but I can't seem to get that to work, what do you think I'm doing wrong?

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Apologies...you need the INTERSECT function. As in

     

    Create the measure :

    Or filter =
COUNTROWS ( INTERSECT ( VALUES ( 'Ind OU'[OU] ), VALUES ( Computer[OU] ) ) )
    + COUNTROWS (
        INTERSECT ( VALUES ( 'Ind Users'[Username] ), VALUES ( Computer[Username] ) )
    )

    Add the [Or filter] to the filter for the visual in the filter pane and set the value to greater or equal to 1:

    I've attached a sample PBIX file

    • FPackermanGTA's avatar
      FPackermanGTA
      Icon for Advocate I rankAdvocate I
      4 years ago

      Thank you very much and for taking the time to provide the gif and pbix file.

       

      Much appreciated.

       

      Cheers