Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help with equivalent PowerBI ISNA and VLOOKUP Function

Hello, Apologies as I’m still new to PowerBI. In PowerBI I have 2 tables that are connected via 1-to-many:   UserLookup – contains simple user data for our application such as their name, start d...
General Comment
Need Help
Tutorial Requests
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous add the following measure, change column, and table name as per your model.

     

    Measure = 
VAR __status = CALCULATE ( LASTNONBLANK ( Timesheet[Status], 1 ) )
RETURN IF ( __status == BLANK(), "Missing", __status )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

Anonymous there are many ways to do this, quick question, do these tables have a relationship? if yes, is it one to many, means, one records in the user table and many records for the same user in a timesheet, or it is one to one, means the only record in the user table and one record for the same user in the timesheet?

 

Solution will be dependent on this relationship.

 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey, thank you for the reply. The relationship between the two tables are in fact one-to-many. 

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous add the following measure, change column, and table name as per your model.

     

    Measure = 
VAR __status = CALCULATE ( LASTNONBLANK ( Timesheet[Status], 1 ) )
RETURN IF ( __status == BLANK(), "Missing", __status )

     

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      WOW! Thank you so much! I kept messing around with LOOKUPVALUE, RELATED, etc. and just could not get it to work right. I truly appreciate your help! 

       

      Do you have a basic source where I can learn more about measures, DAX functions, etc.? I hadn't really seen VAR or even _status used before. 

       

      Thanks again and I will make sure to mark your reply as the solution!

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        Anonymous I usually do very custom training in a batch of 5 which is mainly designed around basics of DAX and then talk about actual business problems. Send me an email directly if you are interested, I give discounts with batch of 3/5/10 and I usually prefer max 5 people in a session. Thanks!

         

        My email is in my signature.