Forum Discussion

PS_78's avatar
PS_78
Helper II
1 year ago
Solved

Read values dynamically

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...
Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    1 year ago

    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"

     

     

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

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's avatar
PSivapuram
Frequent Visitor
1 year ago

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

 

Thanks,

Phani

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.