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'...
Need Help
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