Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Greeting, everyone,
For couple of days can't figure out how I should approach this and got stuck in a corner.
I would like to conditionally fill down the (2) collumns infromation based on criteria so that the values that have to be blank are left blank without filling them down from above.
The criteria would be "Antraste" - which is based on "Vartotojas" = User and "Imone" = Company and "Kontaktas"= Manager (to make it easier changed names in " ".
So what I am struggling with is to fill down the information of "Antraste" based upon Company which it is related to User. If I am trying to do the "fill down" function within the "Edit Queries" it fills dow unconditionally and fills in the blanks where there should be blanks. Same is with the Manager - where there should be blank it fills in one from above.
Thank you for any insights towards the issue that I am facing.
Solved! Go to Solution.
Hi @Anonymous
Thanks to rohitMe’s suggestion, I make a test and it seems to achieve your requirement.
New Antrte = IF ( Sheet1[Antrate] = BLANK (), CALCULATE ( LASTNONBLANK ( Sheet1[Antrate], Sheet1[Antrate] ), FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Vartotojas] ), Sheet1[Imone] = EARLIER ( Sheet1[Imone] ) ) ), Sheet1[Antrate] )
New Kontaktas = IF ( Sheet1[Kontaktas] = BLANK (), CALCULATE ( LASTNONBLANK ( Sheet1[Kontaktas], Sheet1[Kontaktas] ), FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Vartotojas] ), Sheet1[Imone] = EARLIER ( Sheet1[Imone] ) ) ), Sheet1[Kontaktas] )
Best regards
Maggie
Hi @Anonymous
Thanks to rohitMe’s suggestion, I make a test and it seems to achieve your requirement.
New Antrte = IF ( Sheet1[Antrate] = BLANK (), CALCULATE ( LASTNONBLANK ( Sheet1[Antrate], Sheet1[Antrate] ), FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Vartotojas] ), Sheet1[Imone] = EARLIER ( Sheet1[Imone] ) ) ), Sheet1[Antrate] )
New Kontaktas = IF ( Sheet1[Kontaktas] = BLANK (), CALCULATE ( LASTNONBLANK ( Sheet1[Kontaktas], Sheet1[Kontaktas] ), FILTER ( ALLEXCEPT ( Sheet1, Sheet1[Vartotojas] ), Sheet1[Imone] = EARLIER ( Sheet1[Imone] ) ) ), Sheet1[Kontaktas] )
Best regards
Maggie
Thats amazing!
I almost got it working as well - with some bugs but was sufficient.
This however does it perfectly.
Thank you for your time @v-juanli-msft and Everyone!
Hi @Anonymous
I would suggest you that instead of using the fill down option you create a new custom column defining the criteria according to which you need to fill the cell value.
Look at the following problem and solution so you get an idea as to how you can proceed. The solution is I guess not what you are looking for but it should help you to understand what you need to do to acheive your result.
https://community.powerbi.com/t5/Desktop/Filling-Data-Gaps-Conditionally/td-p/148745
Regards
Rohit
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |