Forum Discussion

PowerBIGpig's avatar
PowerBIGpig
Frequent Visitor
6 years ago

Cross reference another table to retrieve text value

Hello! I am trying to reference a table that has a list of activities to a table that has a list of purchases that "should" have the activities as the first characters of the description. E.g. Tabl...
Need Help
PowerBIGpig's avatar
PowerBIGpig
Frequent Visitor
6 years ago

Thanks for your reply !

I'm getting an error which is higlighting "search". It says "Token Literal Expected"

 
v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi PowerBIGpig ,

 

We can use the following measure to meet your requirement:

 

Activity = 
COALESCE (
    CALCULATE (
        MAX ( 'Activities'[Activities] ),
        FILTER (
            'Activities',
            'Activities'[WBS] = 'purchases'[WBS]
                && CONTAINSSTRING ( 'purchases'[Description], 'Activities'[Activities] )
        )
    ),
    "Unknown1"
)

 

 


By the way, PBIX file as attached.


Best regards,

 

  • PowerBIGpig's avatar
    PowerBIGpig
    Frequent Visitor
    6 years ago

    Hi!

    Yes, gave it a go, and it seemed like it was on the way, it was happy with cross-referencing to the other table, however it seems to be very particular in leaving most of the lines as "Unknown"

     

     

     

     

     

     

     

     

     

     

     

    Activity = COALESCE(CALCULATE(max('Activity List'[Activity]), FILTER('Activity List', 'Activity List'[WBS] = 'Acc/Act/Comm/Web'[WBS] && CONTAINSSTRING('Acc/Act/Comm/Web'[Purchasing Item Text], 'Activity List'[Activity]))), "Unknown")

     

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi PowerBIGpig ,

       

      Could you please check if the rows with Unknowns has any matched rows in table  'Activity List' ? (same WBS and Activity is part of Purchasing Item Text)


      Best regards,

       

      • PowerBIGpig's avatar
        PowerBIGpig
        Frequent Visitor
        6 years ago

        Hi v-lid-msft ,

         

        The Activity List is a list of all WBS/Activity Codes, e.g.

        ActivityWBSWork Package
        EW.1.1PROJ-100001EW
        EW.2.1PROJ-100001EW
        EW.3.1PROJ-100001EW
        Unknown1PROJ-100001EW
        SS.2.2PROJ-100002SS
        Unknown2PROJ-100002SS

         

        The Acc/Act/Comm/Web table has all items purchased, it primarily has the WBS (exact as listed above), and the Line description which (usually) has the Activity listed in the start e.g. EW.1.1. If it doesn't have the Activity name in the string, then it can be listed as unknown, but only if the Activity isn't there.