Forum Discussion
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...
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.
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 !!