Read values dynamically

[PS_78]

Hello - Is it possible to read value from a column dynamically? Please consider the scenario below and suggest.

I have a table (Color) with below structure.

Color	Actual	Trgt_White	Trgt_Blue	Trgt_Black
White	1	1	0	0
Blue	2	0	2	0
Black	0	0	0	1

I want to create a new column and compare Actual with Target and populate a color code (Red or Green). In the code below, Variable, _trgtNm would have the target column name. I want to get the value stored in that column into another variable for comparison. 

VAR _color = 'Color'[Color]

VAR _trgtNm = "'Color'[Trgt_" & _color & "]"

Now the Variable, _trgtNm would have the column name such as 'Color'[Trgt_Blue]. I want to read value from this column. Any inputs are appreciated.

Thanks,

PS

[muhammad_786_1 · Accepted solution]

I think It can work for you. You can create a calculated column like that.

Result =

VAR _color = 'Color'[Color]

VAR _actual = 'Color'[Actual]

VAR _target =

    SWITCH(

        _color,

        "White", 'Color'[Trgt_White], -- If _color is "White", return the value from Trgt_White column

        "Blue", 'Color'[Trgt_Blue],  -- If _color is "Blue", return the value from Trgt_Blue column

        "Black", 'Color'[Trgt_Black] -- If _color is "Black", return the value from Trgt_Black

    )

RETURN

    IF(_actual = _target, "Green", "Red") -- If they match, the result is "Green" otherwise, it's "Red"

[PSivapuram]

Thanks @muhammad_786_1 . Yeah, switch is one option but I do not want to use switch. Instead want to see if we can read dynamically (like we can do in SAP ABAP).

But thanks for your suggestion.

Regards,

PS

[ryan_mayu]

@PS_78 

you can do the data transform in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PyS9S0lFyTC4pTcwBMkKK0kviwzMyS1JhHKecUiR2YnK2UqxOtBJMiSEUG4AxSAaq3ggqaIQkAdIMUwrBhkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Color", type text}, {"Actual", Int64.Type}, {"Trgt_White", Int64.Type}, {"Trgt_Blue", Int64.Type}, {"Trgt_Black", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Color"}, "Attribute", "Value"),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value] <> 0)),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value", List.Sum)
in
#"Pivoted Column"

pls see the attachment below

[PSivapuram]

Thanks @ryan_mayu . I am looking to achieve this in DAX.

Thanks,

Phani

[Anonymous]

Thanks for the reply from ryan_mayu and muhammad_786_1   , please allow me to provide another insight:

Hi, @PSivapuram and @PS_78 

Your idea is great, and I'm also going to practice the output of your code, which is indeed the list name you need.

Nevertheless, according to our measured results, I feel regretful to inform you that it turns out to be a by-design one. because your needs are to dynamically compare with the column name and then output the same row value, we in DAX currently do not have the column name that can get the table for the time being, so there is no way to change it.

As an alternative, the responses of ryan_mayu  and muhammad_786_1  are more effective, and you can consider accepting their responses as a solution to help more community members who have similar ideas to you find answers.

If you feel that performance needs to be improved, you can also submit an idea on

https://ideas.fabric.microsoft.com/ and wait for users with the same needs as you to vote for you and help you realize the idea as soon as possible. Many features of our current products are designed and upgraded bed on customers’ feedback.  With requirements like this increase, the problem may well be released in the future.

Thanks for your understanding.
 

Best Regards,

Leroy Lu

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