Combining data from various columns into one column based on value in another column

[AngelikaMcGill]

Hopefully this isn't as complicated as it seems...

I am extracting data from Dynamics and in the extraction I have values that are coming into power bi in various columns instead of one based on the type of value (text, short text, integer, boolean, table, etc).  How would I create a DAX formula that looks at the type of the value "First Type" and returns the value in the column that it corresponds to.  So a value in "Text" in the First Type column would return the value from the "First AnswerText" column.  The report is really messy with 7 value columns instead of one.  Any help would be much appreciated.

[v-haibl-msft · Accepted solution]

@AngelikaMcGill

Does above solution help?

Best Regards,
Herbert

[AngelikaMcGill]

I haven't had a chance to test it yet but it looks like it should work.  Thank you!

[v-haibl-msft]

@AngelikaMcGill

You can unpivot part of the columns as below in Query Editor.

Then create relationship between two tables using the First Type column.

I create a Tag column to indentify the answer using following DAX formula.

Tag = 
IF (
    Table1[First Type] = "ShortText",
    SEARCH ( "Text", Table1[Attribute],, -1 ),
    IF (
        Table1[First Type] = "Table",
        SEARCH ( "First GroupAnswer2", Table1[Attribute],, -1 ),
        IF (
            Table1[First Type] = "MultipleRadio"
                || Table1[First Type] = "MultipleCheckbox",
            SEARCH ( "First Checkbox", Table1[Attribute],, -1 ),
            SEARCH ( Table1[First Type], Table1[Attribute],, -1 )
        )
    )
)

At last, we only need to use the Tag column to get the desired answer.

Answer = 
CALCULATE ( MAX ( Table1[Value] ), Table1[Tag] > 0 )

Best Regards,
Herbert

[Greg_Deckler]

Can you post some sample data and example of the desired output that you want? Having trouble figuring out what you are trying to do here. You may have to pivot/unpivot those columns.

[AngelikaMcGill]

I'm just looking to line up the multiple columns into one as shown below: