Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a measure that evaluates some selections to get a rate table, possibly like the following
sel:= switch(selectedvalue(whatif[column], 0), 1, "v1", 2, "v2", "v3")
and a table of various numbers
code | v1 | v2 | v3 |
abc | 1 | 11 | 5 |
def | 2 | 4 | 16 |
and i'd like to retrieve the value, as if this was valid
abc:= lookupvalue(table[<sel>], table[code], "abc")
Is there a clean way to do this? Avoiding decision blocks like
abc:= switch(sel,
"v1", lookupvalue(table[v1], table[code], "abc"),
"v2", lookupvalue(table[v2], table[code], "abc"),
lookupvalue(table[v3], table[code], "abc")
)
This is cleaner, but still a hack and not sustainable
abc=
var vt1 = SELECTCOLUMNS( FILTER( table, [code] = "abc" ), "col", SWITCH( [sel], "v1", [v1], "v2", [v2], [v3] ) )
return FIRSTNONBLANK( vt1, 1 )
@hansei Honestly, I'm not even sure what exactly you are trying to do or what the expected outcome is that you want but pretty sure that you can't take a text string and suddenly treat it as a column name.
I think the main problem you are having is that you need to unpivot your v1, v2, v3 columns.
The general LOOKUPVALUE equivalent is MAXX(FILTER(...)...) but you still can't magically transform text into something that can be used as a column reference.
Agreed, unpivoting is the easy way to go.
And I didn't think the desire to reference a column dynamically was considered 'magical'. Just like one might use Record.Field(var, "abc") instead of var[abc] in M when the field name was dynamic.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |