Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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
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.
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.
Result = CALCULATE ( FIRSTNONBLANK ( Table1[Answer], 1 ), FILTER ( Table1, Table1[Technical ID] = EARLIER ( Table1[Technical ID] ) && Table1[Question] = "Site ?" ) )
Regards
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
Hey,
I would recomend to use Power Query instead of DAX.
It's very simple using PQ
Hope this suits your needs
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
106 | |
75 | |
44 | |
39 | |
33 |
User | Count |
---|---|
165 | |
90 | |
65 | |
46 | |
43 |