topic Re: Table of multiple values was supplied where a single value was expected in DAX Commands and Tips

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

tomascid — Mon, 13 Mar 2023 19:44:05 GMT

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?

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

tamerj1 — Mon, 13 Mar 2023 20:12:50 GMT

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 )

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

tomascid — Tue, 14 Mar 2023 23:41:09 GMT

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")

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

v-zhangti — Fri, 17 Mar 2023 08:02:14 GMT

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.