asked
DAX Row filter expression
I am trying to implement a row filter using DAX in SSAS tabular model. I have a table1 with below structure. ServiceRequestNumber SubsidiaryName SourceName 5606831 Australia CST 7265513 Germany CST 5516503 United States CST 117112917233865 United States CST 117070515994081 Poland CST 1382689555 United Kingdom CST 7715190 Brazil CST 617030792971767 United Kingdom CST 117080216127490 Taiwan CST 118022317700599 United States CST It should be filtered as per below query. = 'Table1'[ServiceRequestNumber] IN {SELECTCOLUMNS ( FILTER ( CROSSJOIN('Table1',FILTER('Table2','Table2'[UserName] = USERNAME())) ,'Table1'[SubsidiaryName] = 'Table2'[CustomData] && 'Table1'[SourceName] = 'Table2'[SourceName] ) ,"ServiceRequestNumber",'Table1'[ServiceRequestNumber] )} Writing the above query in SSMS -> Role -> Row Filter -> Table1 Query keep on executing and give no result for 2,3 minutes. Table2 structure, UserName CustomData SourceName aaa Argentina CTS Kindly suggest how I can apply this filter on basis of two column Custom and SourceName.
Hi, Can you please try using the following expression in the Row Filter of the role: =Table1[SourceName]=LOOKUPVALUE(Table2[SourceName],Table2[UserName],USERNAME(),Table2[SourceName] , Table1[SourceName] )&& Table1[SubsidiaryName]=LOOKUPVALUE(Table2[CustomData],Table2[UserName],USERNAME(),Table2[CustomData] ,Table1[SubsidiaryName] ) I tested this on my own laptop and it gave me the desired behavior, This solution is based on Microsoft's tutorial [here][1] [1]:
https://docs.microsoft.com/en-us/sql/analysis-services/supplemental-lesson-implement-dynamic-security-by-using-row-filters?view=sql-analysis-services-2017
This worked.... Yipeeeeee... Thank you @tzvikl IT worked perfectly. Below is the query I used. a little tweak to accommodate my requirement. ='Table1'[SourceName]=LOOKUPVALUE('Table2'[SourceName],'Table2'[UserName],MID(USERNAME(), FIND("\", USERNAME(), 1) + 1, LEN(USERNAME()) - FIND("\", USERNAME(), 1)),'Table2'[SourceName], 'Table1'[SourceName],Table2[CustomData],'Table1'[SubSidiaryName] ) && 'Table1'[SubSidiaryName] = LOOKUPVALUE('Table2'[CustomData],'Table2'[UserName],MID(USERNAME(), FIND("\", USERNAME(), 1) + 1, LEN(USERNAME()) - FIND("\", USERNAME(), 1)),'Table2'[SourceName], 'Table1'[SourceName],Table2[CustomData],'Table1'[SubSidiaryName] )
23 People are following this question.
