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 Name and OU( e.g Dept) and the last user that logged into that computer. Notice some computers have had no user login.

 

The other table has Username and the department they work in.

 

                     

 

They have a relationship as follows.

 

What I would like is one slicer that will update the table with all the machines in say the Finance OU and all the machines that the Users in the department Finace have logged into.

 

You can achieve the result I want with the heirarchy slicer but that requires you to search the department and select multiple boxes and require knowledge of how this works which many of the report viewer will not know about.

 

 

If you slice by OU you don;t get computer 5 and slice by Department you miss computer 4 and because they use AND logic if you select both then you miss computer 4 and 5.

Obviously I need a helper column but because the data is in different tables I'm not sure how to go about this. Any ideas?

 

Cheers

 

 

 

 

 

 

 

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

4 Replies

  • 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