How to create a proper Multi-(IN) Clause DAX Query

[PBI_DAX_Newbie]

Hello,

My Scenario

I am trying to implement Dynamic RLS in Power BI and I am trying to add filters into my DAX and failing miserably. I am clearly writing it incorrectly and I also receive an error which I will describe below. And I get the model may be bleh but its what I have to work with.

Image I have the following ugly Schema and I want to pull out all the Invoices that are for a specific User. I would think of it like this in SQL Terms

Select * from Invoices where City in (Select City from Cities where State in (Select State from States where Region in (Select Region from Regions where Region in (Select Region from UserfTORegionMapping where EmailAddress = USERPRINCIPLENAME())))

(of course it would simply using = than ins above but its just one way to look at it)

But I am having no idea how to do this in DAX. I tried playing around with FILTER and IN but going passed a single = or IN and I immediatley and lost.

Thank you Kindly for looking at this and helping

[Anonymous · Accepted solution]

Hi @PBI_DAX_Newbie ,

You can apply a filter in the table 'Invoices' when create a role with the below formula:

'Invoices'[City]
    IN CALCULATETABLE (
        VALUES ( 'Cities'[City] ),
        FILTER (
            ALL ( 'Cities' ),
            'Cities'[State]
                IN CALCULATETABLE (
                    VALUES ( 'States'[State] ),
                    FILTER (
                        ALL ( 'States' ),
                        'States'[Region]
                            IN CALCULATETABLE (
                                VALUES ( 'Regions'[Region] ),
                                FILTER (
                                    ALL ( 'UserfTORegionMapping' ),
                                    'UserfTORegionMapping'[EmailAddress] = USERPRINCIPALNAME ()
                                )
                            )
                    )
                )
        )
    )

In addition, you can refer the following links to get it:

Dynamic Row-Level Security in Power BI with NOT IN or NOT EQUAL Rule - RADACAD

Multi-level access rights in Dynamic Row Level Security - Abylon

Best Regards