Forum Discussion

Rathod's avatar
Rathod
Frequent Visitor
2 years ago
Solved

Return second lookup value instead of first value

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 "[email protected]" logs in, it should return the value "[email protected]", but it is currently returning "[email protected]".

Here is my DAX 

VAR _getRoles = IF(CALCULATE (
FIRSTNONBLANK (Roles_EmailID[Email_IDS], 1 ),
FILTER ( ALL ( Roles_EmailID ), Roles_EmailID[RolesEmailid] = USERPRINCIPALNAME())) = "[email protected]" ,
TRUE(),
[RolesEmailid] = USERPRINCIPALNAME())

RETURN

_getRoles




  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    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's avatar
    Rathod
    2 years ago

    Hi Gautam,

     

    Thanks, its working fine

6 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Rathod
      Frequent Visitor
      2 years ago

      Hi Gautam,

       

      Thanks, its working fine

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Rathod
      Frequent Visitor
      2 years ago

      Hi Rajendra,

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

       

       

    • Rathod's avatar
      Rathod
      Frequent Visitor
      2 years ago

      Hi Rajendra,

      Sorry, its working fine. thank you so much

  • BeaBF's avatar
    BeaBF
    Icon for Super User rankSuper User
    2 years ago

    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