cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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()>

Roles.PNG

Cheers!

Nathan

View solution in original post

1 REPLY 1
Anonymous
Not applicable

@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()>

Roles.PNG

Cheers!

Nathan

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors