Forum Discussion

Sabsy's avatar
Sabsy
Frequent Visitor
7 years ago
Solved

LOOKUPVALUE with multiple results

Hi all,   I am new to Power BI so appologies if this is a silly question but I've been stuck on finding a solution and could really use some guidance.    I have two tables that do not contain a u...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Hope this helps.  You may download my PBI file from here.

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

I have re-read your question but am still unable to understand.  Someone else will help you.  Sorry.

Sabsy's avatar
Sabsy
Frequent Visitor
7 years ago
Would you be able to help with me with the formula of a single measure that can return multiple values in a vlookup into a single cell separated by a comma. That would resolve my problem.
  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Sabsy I've tried below steps to solve your scenario...

     

    Step 1 : Created a Lookup table in "Power Query Editor" 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyNAARRsbxjki0X75SrA5QgREhBcZAthFMgROSgsjUYogKEyDHGKeKWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Contract ID" = _t, Product = _t, CutomerPlan_Old = _t, CustomerPlan_New = _t, Impacted = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract ID", type text}, {"Product", Int64.Type}, {"CutomerPlan_Old", type text}, {"CustomerPlan_New", type text}, {"Impacted", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Impacted] = "No")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Product", "CutomerPlan_Old", "Impacted"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"CustomerPlan_New"}, {{"ContractIDNew", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "CustomContract", each [ContractIDNew][Contract ID]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"CustomContract", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

    The output of above will be as :

     

     

    Step 2 : Create a new table in DAX as below

     

    CustContractNew = 
VAR _CustContractImpacted = SELECTCOLUMNS(CALCULATETABLE(CustContract,CustContract[Impacted]="Yes"),"Contract ID",[Contract ID],"Product",[Product],"ContractIDNew",LOOKUPVALUE(CustContractLKP[CustomContract],CustContractLKP[CustomerPlan_New],[CustomerPlan_New]))
RETURN _CustContractImpacted

    The output will be as below

     

     

    Hope this helps !!