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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |