DAX expression works with imported data but not with direct query mode

[Anonymous]

Following DAX expression works with imported data but not with direct query:

[SourceABCKey] IN SELECTCOLUMNS(FILTER('XYZUserDataAccess', 'XYZUserDataAccess'[UserName]=UserName()),
"Sourcekey"
,[ParameterCode])

There are 3 tables:

Table1 - XYZUserDataAccess - This contains username and Parameter, say SalesDistrict or School.

Table2 - DimParameter, say DimSalesDistrict or DimSchool.

Table3 - FactTable with SalesCount or StudentCount and Parameter - SalesDistrict or School.

Relationship exists between Table2 to Table1, and Table2 to Table3.

Tha above DAX expression works on Table2 in imported mode.

In direct query mode it gives error. Tried various expressions on all 3 tables on various scenarios, but non worked. 

[Anonymous]

Hi @Anonymous

Not all DAXformulas works with direct query mode, theer are some limitation because the way those formulas are evaluated .

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=sql-server-2017

Givea  try to the below opion:

Thanks

Raj

[Anonymous]

Thank you for reply. I will try it out.