Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Calculated Multiple Lookup Values

HELP!  I've taken a new position that utilizes Power Bi heavily and I'm trying to run a new column (A that takes my 'Accessorials' column and references two other tables in order to populate all possible results.  This is coming from a SQL Stored Procedure, which when ran manually, referenced an XML hierarchy file.

 

 

 

 

Accesorial.Text = LOOKUPVALUE(AccessorialModeLTL[Attribute.Text], AccessorialModeLTL[Attribute.Value], '4-1 StoredProcedure Query'[Accessorials])

 

 

 

 

When I run this basic lookup formula, I only get accessorials if they only had one listed attribute (many rows have this field null as they have no accessorials)-- Any row that has more than one accessorial doesn't return anything.  I need a list of all accesorials  delimited by commas, so that we can evaluate the prices based on which "add-ons" were included in this price

 

 

Lookup Table (heiarchy) #1:

 

Lookup Table (heiarchy) #2:

 

I have attached both of the lookup tables in .xlsx format for a more detailed scope of the data and lookup options.  So, I kind of have two questions:

1) What formula can I use so that all lookup values from my two tables are included in the output column?-- Keep in mind I have two lookups  (the first lookup gives a 1 for true and 0 for false, the second lookup actually references varchar lookup values)

2) Is there a better (beginner) way to model & reference this hierarchy so that the end user can only see the ouputted accesorials instead of the [pu_lifgate:1], [trailerfeet:9], [trailerfeet:11], etc. format?

 

Flattened Accessorial Table #1 (True/False)

Flattened Accessorial Table #2 (Varchar)

Original XML Hierarchy File 

 

Any help is greatly appreciated, as I may be in a bit out of my league here.

2 Replies

  • Anonymous , I only got the part of more than one condition, so option is you can add more than one condition

     

    Accesorial.Text = LOOKUPVALUE(AccessorialModeLTL[Attribute.Text], AccessorialModeLTL[Attribute.Value], '4-1 StoredProcedure Query'[Accessorials], <> , <> )

     

    or you can try like

     

    Accesorial.Text = maxx(filter( AccessorialModeLTL, AccessorialModeLTL[Attribute.Value], '4-1 StoredProcedure Query'[Accessorials]
    && AccessorialModeLTL[Col2 ], '4-1 StoredProcedure Query'[Col2]) ,AccessorialModeLTL[Attribute.Text])

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support

    Hi, Anonymous 

    Could you please tell me whether your problem has been solved?

    If yes, you could accept the helpful answer as solution.

    If you still need help, please share more details for further research.

     

    Best Regards,
    Community Support Team _ Eason