Table of multiple values was supplied where a single value was expected

[tomascid]

Hi there - referencing the example data above, I'm trying to create the 'desired output' as a dashboard visual using measures (instead of PowerQuery or calculated columns).

I started with this measure:

HasSPCC = LOOKUPVALUE(Plans[PlanTypeCode],Plans[FacilityId],SELECTEDVALUE(Facilities[FacilityId]))

Translated to English, I'm Facilities [FacilityID] within Plans [FacilityID] column and returning result Plans [PlanTypeCode].

I get this error: "ERROR: A table of multiple values was supplied where a single value was expected."

I assume it's because Plans [FacilityID] is not unique (there can be several different plans for each facility - see yellow highlights). 

Based on that, I assumed that wrapping the measure in a CALCULATE function would work. I tried these two, with no luck:

HasSPCC = CALCULATE(LOOKUPVALUE(Plans[PlanTypeCode],Plans[FacilityId],SELECTEDVALUE(Facilities[FacilityId])),Plans[PlanTypeCode] = "SPCC")

HasSPCC = CALCULATE(LOOKUPVALUE(Plans[PlanTypeCode],KJDatabase_Plans[FacilityId],SELECTEDVALUE(Facilities[FacilityId])),FILTER(Plans,Plans[PlanTypeCode]="SPCC"))

Neither worked, same issue. Any ideas?

[tamerj1 · Accepted solution]

Hi @tomascid 

create a relationship between the two tables. Use a matrix visual.. place Facilities[Facility ID] in the rows of the matrix. Place Plans[PlanTypeCode] in the columns of the matrix and place the following measure in the values of the matrix 

Count = COUNTROWS ( Plans )

[tomascid · Accepted solution]

Solved! https://community.powerbi.com/t5/Desktop/LookupValue-with-Filter/td-p/993672 

Just need to use the second lookup parameters of LOOKUPVALUE:

Has SPCC = LOOKUPVALUE(Plans[PlanTypeCode],Plans[FacilityId],SELECTEDVALUE(Facilities[Id]),Plans[PlanTypeCode],"SPCC")

[v-zhangti · Accepted solution]

Hi, @tomascid 

You can try the following methods.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomascid · Accepted solution]

Solved! https://community.powerbi.com/t5/Desktop/LookupValue-with-Filter/td-p/993672 

Just need to use the second lookup parameters of LOOKUPVALUE:

Has SPCC = LOOKUPVALUE(Plans[PlanTypeCode],Plans[FacilityId],SELECTEDVALUE(Facilities[Id]),Plans[PlanTypeCode],"SPCC")

[tamerj1 · Accepted solution]

Hi @tomascid 

create a relationship between the two tables. Use a matrix visual.. place Facilities[Facility ID] in the rows of the matrix. Place Plans[PlanTypeCode] in the columns of the matrix and place the following measure in the values of the matrix 

Count = COUNTROWS ( Plans )