Forum Discussion
Lookup Value From Within Virtual Table
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 |
Hi diablo9081
You could perform a lookup-equivalent with an expression like this:
SELECTCOLUMNS ( FILTER ( __DataTransformT0, [v_Clinic_Decode] = "<LOOKUP_VALUE>" ), [ClusterId] )Assuming
__DataTransformT0is guaranteed to contain at most one row perv_Clinic_Decode(which should be the case here), this would return a 1x1 table (orBLANK) which is automatically converted to a scalar value where required.Alternatively, you could consider using
DEFINE TABLErather thanDEFINE VARfor__DataTransformT0which 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.
3 Replies
Hi diablo9081
You could perform a lookup-equivalent with an expression like this:
SELECTCOLUMNS ( FILTER ( __DataTransformT0, [v_Clinic_Decode] = "<LOOKUP_VALUE>" ), [ClusterId] )Assuming
__DataTransformT0is guaranteed to contain at most one row perv_Clinic_Decode(which should be the case here), this would return a 1x1 table (orBLANK) which is automatically converted to a scalar value where required.Alternatively, you could consider using
DEFINE TABLErather thanDEFINE VARfor__DataTransformT0which 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.
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.
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.
