Return second lookup value instead of first value

[Rathod]

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

[rajendraongole1 · Accepted solution]

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

[BeaBF]

@Rathod Try with:

Measure =

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] = EARLIER(Roles_EmailID[RolesEmailid])), Roles_EmailID[Email_IDS], , DESC)

            ),

            [Rank] = 2

        )

    )

RETURN

_getRoles

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

BBF

[rajendraongole1 · Accepted solution]

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

[Rathod]

Hi Rajendra,

Sorry, its working fine. thank you so much

[Rathod]

Hi Rajendra,

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

[bhanu_gautam]

@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

[Rathod · Accepted solution]

Hi Gautam,

Thanks, its working fine