cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sanct
Frequent Visitor

Replace Values in Row with Values from another specific cell

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:

dummyData.png

 

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.

1 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:

PBIDesktop_f1iGbBOScM.png

Best Regards,

Teige

View solution in original post

4 REPLIES 4
TeigeGao
Solution Sage
Solution Sage

Hi @sanct ,

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

Best Regards,

Teige

sanct
Frequent Visitor

Thanks for the response @TeigeGao ! 

 

Ideally the end result would look something like this:

 

dummyData2.png

 

Although some approximation of this is good too.

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:

PBIDesktop_f1iGbBOScM.png

Best Regards,

Teige

View solution in original post

sanct
Frequent Visitor

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 🙂

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors