Compares 2 list with and give a third value

[plalau]

Hello,

I have 3 tables:

Table 1 is a list of companies, with ID and CompanyName (other columns are irrelevant)
Table 2 is a list of Roles, without ID, just Rolename and a second column with Enumlist to Company. Reason is that a role can be available for multiple companies.

In Table 3 there is a list of documents, with ID, DocumentName, For_use_in_Companies (Enumlist) and Roles.

I am looking for the suggested Roles formula.

When I enter a new document and I select multiple Companies, then all the roles are gone. If I do this with 1 company, I get the roles valid for that company. I wanted the complete list of roles for both companies.

[Joseph_Seddik]

The expression would depend on how the references are setup between your tables. But here's a quick answer:

SELECT(Roles[roleID], IN([_ThisRow].[Companies], [roleID].[Companies]))

This would return the roles common among the selected companies.

[plalau]

Sorry it took me so long to reply. Thank you for your reply, but it did not work for me.

I do not understand "how the references are setup between your tables". That's a lack of knowledge from my side. As far as I can see there is only references made in the EnumList.

Dutch to English:
"Rollen" is "Roles"
"Toegepast bij" is "Used for Companies"
"Bedrijven" is "Companies"

In the table Documents (Dutch: Documenten) there is a field called "Belongs to companies" (Dutch: Behoort bij bedrijf) which is also an EnumList of the table Companies.

Maybe this helps you to have a more detailed information about the references?

[Joseph_Seddik]

No problem. Would you please:

1. Show the exact expression you have used.
2. Show the result that didn't work for you.
3. Tell me your desired result. 

Thanks.

[plalau]

I am trying a work around. It is a security filter on Table "Actionlist". This table has records for different companies, therefore the column"Behoor bij" (Belongs to) is a EnumList with reference to the Company table. Company has [ID] and {Name] (as label).

My table with persons has the column "Actief voor" (active for) also EnumList with reference to the Company table.

I use the query for selecting only these rows of the Actionlist that intersect the 2 lists.

ISNOTBLANK(
    INTERSECT(
        SPLIT([Behoort bij],";"),
        SPLIT(
             SELECT(Personen[Actief voor],
                  OR(
                        CONTAINS([Mailadres werk],USEREMAIL()),
                        CONTAINS([Mailadres privé],USEREMAIL())
                   )
            ),";"
       )
   )
)

This query gives me no results. Also when I use this query without the ISNOTBLANK().