Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

New Column based on values of 2 other columns

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 :

 

Source.jpg

 

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 :

 

results.jpg

 

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

 

Thank you for your help !

 

Regards

Ph

1 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. Smiley Happy

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

c2.PNG

 

Regards

View solution in original post

5 REPLIES 5
TomMartens
Super User
Super User

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.



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Hi @Anonymous,

 

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

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

c2.PNG

 

Regards

Anonymous
Not applicable

Hi @v-ljerr-msft,

 

That works perfectly well for my case ! I will cogitate about the formula structure Smiley Very Happy .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

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
    2017-08-12_7-59-53.png
  • This should return the following result
    2017-08-12_8-04-42.png
  • Mark your base table and choose Home -> Combine -> Merge Queries
    2017-08-12_8-07-14.png
  • Finally, expand the columns, just choose the Answer columns that contains the name of the site and you are done
    2017-08-12_8-10-03.png

Hope this suits your needs



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.