New Column based on values of 2 other columns

[Anonymous]

Hi Community,

This is my first post because I didn't find on the forum how to resolve my problem (usually it is great mine of information 🙂 ).

I have a data source which is structured like this :

I would like to create a new column whose values are based on the text answer of a specific question (in this case, the question ask the site...it is not necessary at the same place) for each instanciation of a survey (which is materalized by column Technical ID)

In concret word, the result should be :

I have done plenty of tests with filters functions but don't find a correct approach.

Thank you for your help !

Regards

Ph

[TomMartens]

Hey,

I have to admit that I have no idea, what the rule looks like, to determine the value for the new column.

Please elaborate your requirement in more detail.

[Anonymous]

Thank you for your first help.

The rule is the following : to fill the new column, you search the answer to a specific question (in this case the question asks the site for example) associated to a technical ID and you repeat the found value for each line with the same technical ID; for a different technical ID, you put the answer to this question associated to this id)

I have a very simple solution in the case that the specific question is placed in the first rank of instanciation (if question = site, then asnwer, else empty) and you use fill empty value to bottom to fill the column)

With Excel, I used to play with vlookup or index(match)) functions which worked great.

I did some tests with allexcept and filter functions and conditions,but didn't achieve to do this.

Regards

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

Based on my test, you should be able to use the formula(DAX) below to add the calculate column in your scenario. 

Result = 
CALCULATE (
    FIRSTNONBLANK ( Table1[Answer], 1 ),
    FILTER (
        Table1,
        Table1[Technical ID] = EARLIER ( Table1[Technical ID] )
            && Table1[Question] = "Site ?"
    )
)

Regards

[Anonymous]

Hi @v-ljerr-msft,

That works perfectly well for my case ! I will cogitate about the formula structure .Thank you

Thank you also @TomMartens, usage of query was not totally suitable in my scenario but I will look also this approach.

See you.

Ph

[TomMartens]

Hey,

I would recomend to use Power Query instead of DAX.

It's very simple using PQ

• Open the QueryEditor
• Mark the table in the queries pane choose Copy and paste the query as new query, rename the query to "sites" or what ever suits you
• Mark the column Question and choose the DropDown Arrow to open the Filter Dialog, enter "Site ?" as filter
  
• This should return the following result
  
• Mark your base table and choose Home -> Combine -> Merge Queries
  
• Finally, expand the columns, just choose the Answer columns that contains the name of the site and you are done
  

Hope this suits your needs