Lookup Value From Within Virtual Table

[diablo9081]

I need some help with how to lookup a value from within a virtual table. LOOKUPVALUE doesn't seem to work with virtual tables.

I've got the following:

DEFINE
VAR __DS0Core = 
	SUMMARIZECOLUMNS(
		'DASHBOARD_VW'[_Clinic_Decode],
		"v_Charting_X", '_Measures'[_Charting_X],
		"v_Charting_Y", '_Measures'[_Charting_Y]
	)

VAR __DataTransformT0 =
	{
		KMeansClustering(
		SELECTCOLUMNS(
			__DS0Core,
			"v_Clinic_Decode", 'DASHBOARD_VW'[_Clinic_Decode],
			"v_Charting_X", '_Measures'[_Charting_X],
			"v_Charting_Y", '_Measures'[_Charting_Y]
		),
		{("[v_Charting_X]", "Attribute"),
			("[v_Charting_Y]", "Attribute"),
			("[v_Clinic_Decode]", "Item")},
		{("[v_Clinic_Decode]", BLANK()),
			("ClusterId", "ClusterId")},
		-1
		)
	}

 
I end up with a virtual table __DataTransformT0 with two columns, [v_Clinic_Decode] and [ClusterId]. How can I lookup the [ClusterId] column value using the [v_Clinic_Decode] value from within this virtual table? LOOKUPVALUE works when the table is in the model, but it's just a virtual table here.

v_Clinic_Decode	ClusterId
x1234	2
b1237	3
d2356	1

[v-ssriganesh]

Hello @diablo9081,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-priyankata]

Hi @diablo9081 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@OwenAuger Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[OwenAuger · Accepted solution]

Hi @diablo9081 

You could perform a lookup-equivalent with an expression like this:

SELECTCOLUMNS (
    FILTER ( __DataTransformT0, [v_Clinic_Decode] = "<LOOKUP_VALUE>" ),
    [ClusterId]
)

Assuming __DataTransformT0 is guaranteed to contain at most one row per v_Clinic_Decode (which should be the case here), this would return a 1x1 table (or BLANK) which is automatically converted to a scalar value where required.

Alternatively, you could consider using DEFINE TABLE rather than DEFINE VAR for __DataTransformT0 which would create a table for the duration of the query. You could then reference this table as though it were a model table in an expression like this:

LOOKUPVALUE (
    __DataTransformT0[ClusterId],
    __DataTransformT0[v_Clinic_Decode], "<LOOKUP_VALUE>" )
)

See here.