Forum Discussion

TechMG's avatar
TechMG
Frequent Visitor
2 years ago

Convert SQL to DAX

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

"Need Help"
SQL to DAX

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

    • TechMG's avatar
      TechMG
      Frequent Visitor
      2 years ago

      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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.