Filter DIM table based on two fields in an unrelated table

[CathyWork]

Hello all,

I'm hoping this will be a fairly straightforward question for the group.  I've tried several solutions I've found on this forum, but I haven't achieved the results I want.  I'm new to learning DAX, so I'm not sure if it's my data model or I'm simply choosing the wrong functions.

I have a list from SharePoint online that has a list of applications used by our organization (FACT table), the departments that utilize the application, the group who manages the application, and the primary and secondary admins.  In my first approach, I created DIM tables for Primary Admin, Secondary Admin from the internal SP Users info, and Department from the original (FACT) table.  Each of the DIM tables have bi-directional relationships with the FACT table.  I was then able to create visuals that would cross filter each other (e.g. selecting the visual with just the applications, would filter the Deparment, Managed By, PrimaryAdmin and SecondaryAdmin, etc).  Next I created a table showing all applications and slicer with all Admins (new DIM), where the user could chose an Admin and see all applications where they were listed as either Primary or Secondary.

Now I have a request to remove inactive users from the visuals.  The problem, my Users are pulling from SharePoint Online and not Azure or on prem AD.  This is because the end users do not have access to users in AD or Azure, but can view the information from SharePoint.  However, SharePoint Online doesn't automatically remove inactive users.   I tried to resolve this by recreating my Primary and Secondary Admin tables from the FACT table, but I ended up with duplicates using the following: 

PrimaryAdmin = SELECTCOLUMNS(

      CALCULATETABLE(

        'Central Application List',

        NOT (ISBLANK('Central Application List'[Primary Administrator.email])

        )),

        "PrimaryEmail", 'Central Application List'[Primary Administrator.email],

        "PrimaryName", 'Central Application List'[Primary Administrator.title]

      )

Can I create a table using something similar to a SELECT DISTINCT?

With the table visual showing all applications, I was able to create a custom Measure to filter the table based on the name in the slicer using the following: 

FilterPS =

VAR a =

    IF (

        SELECTEDVALUE('Central Application List'[Primary Administrator.title])

          IN VALUES(AdminUsers[Name])

            || SELECTEDVALUE('Central Application List'[Secondary Administrator.title])

                IN VALUES(AdminUsers[Name]),

        1,

        0

    )

RETURN

    IF(ISFILTERED(AdminUsers[Name]),a,0)

My other problem is how to create the relationships. How do I filter the AdminUsers DIM based on if they are in the list of Primary or Secondary Admins?  I can't relate the Primary and Secondary DIMs to the Admin DIM without creating an inactive relationship.  I tried relating one to the Email field and the other to the Name field, but I have two people with the same 'Name' but different email addresses. I also can't relate it back to the FACT table for the same reason.  

[CathyWork]

Hi Liu Yang,

Thanks for the SQL info, I may use it in a different project.  I'm familiar with SQL, but I was trying to find a DAX equivalent to the SELECT DISTINCT since my original data is in SharePoint and not in a SQL DB.

Cathy

[foodd · Accepted solution]

Hello @CathyWork , you can achieve the equivalent of SELECT DISTINCT using the DISTINCT function. 

Source references as a primer to DAX - DISTINCT is included below:

DISTINCT (column) function (DAX) - DAX | Microsoft Learn

DISTINCT – DAX Guide

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!

[v-yangliu-msft]

Hi  @CathyWork ,

Are you referring to creating a table using SQL statements in Power BI? As far as I know, Power BI does not currently support this feature. The supported languages in Power BI are DAX functions and M language. You can create calculated tables using DAX or utilize Power Query and M language to create tables.

Additionally, you can find the ‘Advanced options – SQL statement’ in the SQL Server connection interface within Power BI. Here, you can input SQL statements to preprocess tables.

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

You might consider creating a bridging table to join relationships:

Many-to-many relationship guidance - Power BI | Microsoft Learn

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.