Replace Values in Row with Values from another specific cell

[sanct]

Hi All,

I'm trying to replace the values in one cell with the values from another specific cell. I'll start with the data model I'm using:

In a nushell, I have the results of an extensive survey that was reworked to fit a generally useable data model that has a respondent dimension table in the centre, and a series of fact tables surrounding it. Everything is filtered by a ResponseID.

Unfortunately, some of the questions allowed for open ended entries that are problematic to deal with for someone who isn't used to working with complex conditional logic and lookup tables.

Here's a dummy data example of what I'm dealing with:

In order to provide summary data of my stores, I grouped the stores by their major "Types" (i.e. categories). Assume there are many other categories in the "Category" section. In an initial pass, I grouped all of the "Others" into the "Miscellaneous" group. Unfortunately, a significant chunk of responses are labelled miscellaneous. When I look at the "Comment" values, I can see that some of the stores could be fitted into existing categories, but I'm not sure how to make that work.

Also, "Comment1" corresponds to "Other1" and so on.

I have considered building a bridge table consisting of only comment values, that allows me to elaborate on miscellaneous results, but that's not exactly what I'm after. I would like to be able to group these entries into the right categories right away.

Is there a way of building a conditional column that would replace any later "Other1" using the concordance of both the value "Other1" and the specific "ResponseID"?

Any advice would be appreciated.

[TeigeGao]

Hi @sanct ,

Could you please share the expected result to us for analysis?

Best Regards,

Teige

[sanct]

Thanks for the response @TeigeGao ! 

Ideally the end result would look something like this:

Although some approximation of this is good too.

[TeigeGao · Accepted solution]

Hi @sanct ,

Please refer to the following DAX query:

CalculatedCategory =
CALCULATE (
    MIN ( Table1[Value] ),
    FILTER (
        ALL ( Table1 ),
        Table1[ResponseID] = EARLIER ( Table1[ResponseID] )
            && (
                Table1[Category] = EARLIER ( Table1[Category] )
                    || RIGHT ( Table1[Category], 1 ) = RIGHT ( EARLIER ( Table1[Category] ), 1 )
            )
            && Table1[Value] <> BLANK ()
            && Table1[Value] <> "Yes"
    )
)

The result will like below:

Best Regards,

Teige

[sanct]

Thank you so much @TeigeGao  !

It took a little tweaking on my part to make it work with my actual dataset (there were additional categories to consider) but I was able to make it work.

I'm going to keep this formula handy 🙂