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

Question

Hello,&nbsp;My ScenarioI 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.&nbsp;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&nbsp;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)&nbsp;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.&nbsp;Thank you Kindly for looking at this and helping&nbsp;

anonymous · Accepted Answer

Hi&nbsp;PBI_DAX_Newbie&nbsp;,
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

Forum Discussion

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

1 Reply

Related content

Proper duration counting

Multi level hierarchy in Gantt chart by microsoft corporation

How to create proper Time zone convertions, including DST

Change Unit in Multi-Row Card visual

Multi selection in power bi