Reverse filter for multiple selections

[Travelling_Kath]

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 Assigned	User principal name
Microsoft Power Automate Free	oliver@abccorp.com.au
Microsoft 365 E5	oliver@abccorp.com.au
Microsoft Power Automate Free	sylvia@abccorp.com.au
Power BI Pro	sylvia@abccorp.com.au
Office 365 E3	sylvia@abccorp.com.au
Office 365 E3	andrew@abccorp.com.au
Microsoft Power Automate Free	karen@abccorp.com.au
Power BI Pro	karen@abccorp.com.au
Microsoft 365 Audio Conferencing	karen@abccorp.com.au
Microsoft 365 E3	karen@abccorp.com.au
Office 365 E3	joel@abccorp.com.au
Microsoft Power Automate Free	kylie@abccorp.com.au
Microsoft 365 E5	kylie@abccorp.com.au
Enterprise Mobility + Security E3	kath@abccorp.com.au
Power BI Pro	kath@abccorp.com.au
Office 365 E3	kath@abccorp.com.au
Microsoft Power Automate Free	kath@abccorp.com.au
Microsoft Power Automate Free	fred@abccorp.com.au
Microsoft 365 E3	fred@abccorp.com.au

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!

[v-tianyich-msft]

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]

Thanks, Scott @v-tianyich-msft . 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.

[v-tianyich-msft]

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

[Joe_Barry]

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]

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.