Forum Discussion

Catalizt's avatar
Catalizt
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
Solved

vso identity fields converted to weird text strings

Hi there! 

I'm using the OData connector to pull VSO work item data into Power BI. 

Unfortunately, for some reason the contents of the "Identity" fields (e.g. Assigned to) are being converted to long alphanumeric strings.

Here is how the data appears in VSO

 

Here is how that data appears in Power BI.

 

 

I'm sure it's user error. Anyone know what my error might be? 

Thank you! 

 

Need Help
  • Catalizt's avatar
    Catalizt
    8 years ago

    Thank you!

    Unfortunately I had several fields that all needed this matching so I've created columns using the LOOKUPVALUE function. Works like a charm!

     

    Assigned To = LOOKUPVALUE(Users[UserName],Users[UserSK],WorkItems[AssignedToUserSK])

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    I wonder if that is a user unique id and you need to pull in another table to relate the user information to that unique id.

    • Catalizt's avatar
      Catalizt
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      Yes! 

      I found the "Users" table and have brought it in. 

      I think the next thing to do is create calculated columns in my original table which contain the related value. 

      Example: 

      Assigned to = (RELATED('Users'[UserName]))

       

      Is there a better way? 

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago
        Catalizt wrote:

        Yes! 

        I found the "Users" table and have brought it in. 

        I think the next thing to do is create calculated columns in my original table which contain the related value. 

        Example: 

        Assigned to = (RELATED('Users'[UserName]))

         

        Is there a better way? 

        Catalizt

        So I think the assignment table and user tables has a proper many:one relationship.

         

        Besides creating a calculated column, you can do nothing. Then in a chart, like table chart, drag some fields from assignment table and also the name from user table, the relationship would find the related username.

         

        Or you can create some measure just like 

        Measure  =
CALCULATE (
    SUM ( assigment[value] ),
    FILTER ( assigment, RELATED ( user[username] ) = "name1" )
)