cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sanct Frequent Visitor
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

Accepted Solutions
Community Support Team
Community Support Team

Re: Replace Values in Row with Values from another specific cell

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
Community Support Team
Community Support Team

Re: Replace Values in Row with Values from another specific cell

Hi @sanct ,

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

Best Regards,

Teige

sanct Frequent Visitor
Frequent Visitor

Re: Replace Values in Row with Values from another specific cell

Thanks for the response @TeigeGao ! 

 

Ideally the end result would look something like this:

 

dummyData2.png

 

Although some approximation of this is good too.

Community Support Team
Community Support Team

Re: Replace Values in Row with Values from another specific cell

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
Frequent Visitor

Re: Replace Values in Row with Values from another specific cell

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
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 151 members 1,680 guests
Please welcome our newest community members: