topic Re: Return second lookup value instead of first value in DAX Commands and Tips

Return second lookup value instead of first value

Rathod — Tue, 27 Aug 2024 04:19:32 GMT

Hi All,

Could someone please assist me in retrieving the second lookup value instead of the first one? Here are the details:

Table Name: Roles_EmailID

When the user "abcd@gmail.com" logs in, it should return the value "superuser@gmail.com", but it is currently returning "test@gmail.com".

Here is my DAX

VAR _getRoles = IF(CALCULATE (
FIRSTNONBLANK (Roles_EmailID[Email_IDS], 1 ),
FILTER ( ALL ( Roles_EmailID ), Roles_EmailID[RolesEmailid] = USERPRINCIPALNAME())) = "superuser@gmail.com" ,
TRUE(),
[RolesEmailid] = USERPRINCIPALNAME())

RETURN

_getRoles

Re: Return second lookup value instead of first value

bhanu_gautam — Tue, 27 Aug 2024 07:18:01 GMT

Rathod , You can try using RANKX for this

VAR _getRoles =
CALCULATE (
FIRSTNONBLANK ( Roles_EmailID[Email_IDS], 1 ),
FILTER (
ADDCOLUMNS (
FILTER ( ALL ( Roles_EmailID ), Roles_EmailID[RolesEmailid] = USERPRINCIPALNAME() ),
"Rank", RANKX ( FILTER ( ALL ( Roles_EmailID ), Roles_EmailID[RolesEmailid] = USERPRINCIPALNAME() ), Roles_EmailID[Email_IDS], , ASC, DENSE )
),
[Rank] = 2
)
)

RETURN
_getRoles

Re: Return second lookup value instead of first value

rajendraongole1 — Tue, 27 Aug 2024 07:27:33 GMT

Hi Rathod -The rank must correctly differentiate between the different email IDs associated with each RolesEmailid.

create a measure:

SecondEmailID =

CALCULATE(

FIRSTNONBLANK(abcdef[Email_IDS], 1),

FILTER(

abcdef,

abcdef[RolesEmailid] = USERPRINCIPALNAME() &&

abcdef[EmailRank] = 2

)

Verify that the value returned by USERPRINCIPALNAME() matches exactly with the values in the RolesEmailid column.

Hope this helps

Re: Return second lookup value instead of first value

BeaBF — Tue, 27 Aug 2024 07:30:59 GMT

Rathod Try with:

Measure =

VAR _getRoles =

CALCULATE (

FIRSTNONBLANK (

Roles_EmailID[Email_IDS],

FILTER (

ADDCOLUMNS(

FILTER ( ALL ( Roles_EmailID ), Roles_EmailID[RolesEmailid] = USERPRINCIPALNAME()),

"Rank", RANKX(FILTER(ALL(Roles_EmailID), Roles_EmailID[RolesEmailid] = EARLIER(Roles_EmailID[RolesEmailid])), Roles_EmailID[Email_IDS], , DESC)

[Rank] = 2

)

RETURN

_getRoles

if it's ok please accept my answer as solution.

BBF

Re: Return second lookup value instead of first value

Rathod — Tue, 27 Aug 2024 07:41:41 GMT

Hi Rajendra,

Thanks for your swift response, i tried with your dax but unfortunatly getting below error

Re: Return second lookup value instead of first value

Rathod — Tue, 27 Aug 2024 07:49:00 GMT

Hi Rajendra,

Sorry, its working fine. thank you so much

Re: Return second lookup value instead of first value

Rathod — Tue, 27 Aug 2024 07:49:51 GMT

Hi Gautam,

Thanks, its working fine