Convert SQL to DAX

[TechMG]

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

[v-tangjie-msft]

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]

Hi @v-tangjie-msft 

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.

[v-tangjie-msft]

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. 

[TechMG]

Hi @v-tangjie-msft ,

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