Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |