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!
Solved! Go to 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
@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
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
105 | |
77 | |
72 | |
49 | |
47 |
User | Count |
---|---|
159 | |
86 | |
80 | |
68 | |
66 |