How to filter a (Power Query) table using a calculated Measure?

[Anonymous]

I have a table: Department that has columns [DepartmentName], [Domain], etc. I have a Measure 'Department'[Measure_UserDomain], which uses the DAX function USERPRINCIPALNAME() with some text extraction DAX expression to retrieve the currently logged in User's DOMAIN such as "dept1.com". I am looking for a way to filter the Department table such that only the rows matching 'Department'[Domain] = 'Department'[Measure_UserDomain] are included in this table. 

I have this portion of the DAX expression in the Power Query - Advanced Editor which works after hard coding the literal value "dept1.com":

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Domain] = "dept1.com")

If however, I replace this hardcoded literal value "dept1.com" with the Measure: 'Department'[Measure_UserDomain], then the DAX expression fails saying that I can only have a literal value:

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each [Domain] = 'Department'[Measure_UserDomain])

Let me know how to filter the rows to limit only the matching rows to be in this Power BI Direct Query based table.

Thank You!

[Anonymous · Accepted solution]

@Anonymous 

1. Power BI has 2 languages - M (Power Query Scripting) and DAX. 

2. Power Query loads tables - think of it as an ETL process, while DAX can be ETL also (Calculated Tables and Calculated Columns) but also is the report run-time language of Power BI. Therefore, you must count on DAX, and not M, to do the Row-Level Security.

3. How to solve this issue? Create a Role, and apply the row-level security on there with your DAX. On the Department table,  [Domain] = <your text extraction on USERPRINCIPALNAME()>

Cheers!

Nathan