topic Re: Convert SQL to DAX in DAX Commands and Tips

Convert SQL to DAX

TechMG — Wed, 10 Jan 2024 19:58:52 GMT

Hi, I have a paginated report which access the source as Power BI data model, both are deployed in service. I have set of parameters exist in Paginated Report. For ex. ProgramName. It contains list of programnames and use will select the respective program for seeing the data. Now, we have RLS implemented in data model and it is working fine. However, the user can see all the programs in parameter rather he want see only his program. I am planning to restrict the program names in parameter based on user logged in to the system. I have tried below but no luck.

SQL:

select distinct P.ProgramId ,P.ProgramName ,DU.InternalEmailAddress from gold.programid P INNER Join gold.dimclinic C ON P.ProgramId = C.ProgramId INNER Join gold.userclinicbridge UC ON C.ClinicId = UC.ClinicId INNER JOIN gold.dimuser DU ON DU.UserId = UC.UserId WHERE DU.InternalEmailAddress = @UserId

DAX:

EVALUATE ADDCOLUMNS( 'Program'[ProgramId], 'Program'[ProgramName], LOOKUPVALUE('dimclinic'[ProgramId],'Program'[ProgramId]), LOOKUPVALUE('userclinicbridge'[ClinicId],'dimclinic'[ClinicId]), "InternalEmailAddress",LOOKUPVALUE('dimuser'[InternalEmailAddress],'dimuser'[UserId],'userclinicbridge'[ClinicId]) )
I need to apply filter as I mentioned in SQL. As I am new to DAX, seeking some help. Thanks

Re: Convert SQL to DAX

Anonymous — Thu, 11 Jan 2024 02:18:41 GMT

Hi TechMG ,

Please try:

EVALUATE ADDCOLUMNS( FILTER( 'Program', LOOKUPVALUE( 'dimuser'[InternalEmailAddress], 'dimuser'[UserId], 'userclinicbridge'[UserId] ) = userprincipalname() ), 'Program'[ProgramId], 'Program'[ProgramName], LOOKUPVALUE('dimclinic'[ProgramId],'Program'[ProgramId]), LOOKUPVALUE('userclinicbridge'[ClinicId],'dimclinic'[ClinicId]), "InternalEmailAddress", LOOKUPVALUE( 'dimuser'[InternalEmailAddress], 'dimuser'[UserId], 'userclinicbridge'[ClinicId] ) )

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Convert SQL to DAX

TechMG — Thu, 11 Jan 2024 02:47:03 GMT

Hi Anonymous

Thanks for your quick response. I have executed the query and getting below error.

Query (13, 5) Too few arguments were passed to the LOOKUPVALUE function. The minimum argument count for the function is 3.

Re: Convert SQL to DAX

Anonymous — Thu, 11 Jan 2024 06:10:03 GMT

Hi TechMG ,

I'm sorry I changed my dax.

Table = FILTER(ADDCOLUMNS(SELECTCOLUMNS('P',"ProgramId",[ProgramId],"ProgramName",[ProgramName]),"InternalEmailAddress",LOOKUPVALUE( 'dimuser'[InternalEmailAddress], 'dimuser'[UserId], 'userclinicbridge'[ClinicId] )),[InternalEmailAddress]=userprincipalname())

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Convert SQL to DAX

TechMG — Thu, 11 Jan 2024 14:10:23 GMT

Hi Anonymous ,

Tried the above solution and it is saying Incorrect syntex Table.