Forum Discussion

Travelling_Kath's avatar
Travelling_Kath
New Member
2 years ago

Reverse filter for multiple selections

I'm doing analysis on Microsoft 365 licenses assigned to accounts in Entra ID. As some organisations have a requirement for certain license assignments per account, for example O365 E3 and EMS E3, I'm wanting to get a list of accounts that don't have the selected licenses assigned to them.

I've created an unrelated table with the list of licenses which I've added as a slicer, however I've tried a couple of formulas but they only give me a list of accounts with either of the selected licenses assigned.

Sample data for users table:

License AssignedUser principal name
Microsoft Power Automate Free[email protected]
Microsoft 365 E5[email protected]
Microsoft Power Automate Free[email protected]
Power BI Pro[email protected]
Office 365 E3[email protected]
Office 365 E3[email protected]
Microsoft Power Automate Free[email protected]
Power BI Pro[email protected]
Microsoft 365 Audio Conferencing[email protected]
Microsoft 365 E3[email protected]
Office 365 E3[email protected]
Microsoft Power Automate Free[email protected]
Microsoft 365 E5[email protected]
Enterprise Mobility + Security E3[email protected]
Power BI Pro[email protected]
Office 365 E3[email protected]
Microsoft Power Automate Free[email protected]
Microsoft Power Automate Free[email protected]
Microsoft 365 E3[email protected]

 

Licenses table list:

License Assigned
Microsoft Power Automate Free
Microsoft 365 E5
Power BI Pro
Microsoft 365 E3
Office 365 E3
Microsoft 365 Audio Conferencing
Enterprise Mobility + Security E3

 

 

I found some code for reverse filters, however it doesn't give me what I need:

 

 

ShowNonCompliant = 
if(max('users'[License Assigned]) in ALLSELECTED('Licenses'[License Assigned]) && COUNTROWS(ALLSELECTED('Licenses'[License Assigned])) <> countrows(all('Licenses'[License Assigned])), "y", "n")

 

 

It only gives me accounts with either of the licenses assigned, where I'd like to know which accounts don't have them both assigned (for example Andrew, Joel and Sylvia).

Hoping someone can help!

 

Need Help

5 Replies

  • Joe_Barry's avatar
    Joe_Barry
    Solution Sage
    2 years ago

    Hi Travelling_Kath 

     

    Create a one to many relationship between the Licences table and teh USer table on the Licence column.

    Add the Column from the Licence Table to a table visual then the User Principal Name too. On the right side of the screen go to the Build Table and right click on one of the column names and choose the option "Show items with no data"

     

    Hope this helps

    Joe

    • Travelling_Kath's avatar
      Travelling_Kath
      New Member
      2 years ago

      Thanks for your response, Joe_Barry . Unfortunately it gives me all users with any of the licenses selected, rather than those with only the selected licenses.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Travelling_Kath ,

     

    I made a simple sample, you can check the results as follows:

    Selected = 
CONCATENATEX(
    VALUES('Table 2'[Desc]),
    'Table 2'[Desc],
    ","
)
 
Matched = 
VAR Selected = [Selected]
VAR DescList =
CALCULATETABLE(
    VALUES('Table'[Desc]),
    ALLEXCEPT('Table', 'Table'[ID])
)
RETURN
IF(
    CONTAINSSTRING(
        Selected,
        CONCATENATEX(DescList, 'Table'[Desc], ",")
    ),
    1,
    0
)

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

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

    • Travelling_Kath's avatar
      Travelling_Kath
      New Member
      2 years ago

      Thanks, Scott Anonymous . It's working, but only if the user (ID) has only 2 licenses (Desc). It doesn't seem to work if they only have 1 license, or have 3 or more licenses.

      From your example, if ID 1 had D as well as A and C, or if ID 4 only had A.

      Is there an easy way of accommodating for this scenario?

      Thanks, Kath.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Travelling_Kath ,

         

        I'm sorry to say that it doesn't seem to work in my experience. Because there is no such concept as full equality in DAX, either the IN operator or CONTAINSSTRINGEXACT, they both mean containment not full equality.

         

        Best regards,
        Community Support Team_ Scott Chang