How to convert SQL query into DAX?

Question

I am trying to convert a SQL query into DAX so I can create a row level security (table filter DAX expression) on my Power BI Report.

I am trying to check whether the user is equal to the user I have specified AND in the country I specified.

SELECTCOLUMNS(Dim_Unit, “Country”, Username() = 'P\andy.v', AND ( “Country”, "Germany" ))

I have tired to above but i dont understand the Syntax of DAX (https://dax.guide/selectcolumns/ using this as a guide to replace SQL query)

Below is what i should replicate:

SELECT      DISTINCT Country
    FROM        dbo.Dim_Unit
    WHERE       (@UserID IN ('P\andy.vy', 'C\avanston', 'P\gregy.dy', 'PR\ARah', 'PR\fes', 'P\gwen.l'))
            OR  (@UserID IN ('P\liz.trum', 'C\ltrumitch2') AND Country IN ('USA', 'Canada'))
            OR  (@UserID IN ('P\ant.fischer') AND Country IN ('Germany'))
            OR  (@UserID IN ('C\dshorney') AND Country IN ('Singapore', 'Malaysia', 'Hong Kong', 'Indonesia'))
            OR  (@UserID IN ('C\mmillard', 'C\mmillard-ext') AND Country IN ('Australia'))
            OR  (@UserID IN ('C\emarkiyewicz') AND Country NOT IN ('USA', 'Canada'))

    ORDER BY    Country

The expected result is that Power BI will be able to show the correct data for the correct individual based on the specified country, users with no country assigned can see all data.

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2019-05-24T13:31:27

Instead create a table or query that returns (UserId,Country) for all the permitted pairs. Import that query as a table called UserCountry_RLS, give it a relationship to the Country table, with bi-directional crossfiltering, and put a simple DAX RLS filter on it, and hide it from report users.

How to convert SQL query into DAX?

1 Answers