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

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!