cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
iHickUp Regular Visitor
Regular Visitor

Fill down collumn information based on condition

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.

Sketch.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Fill down collumn information based on condition

Hi @iHickUp

Thanks to rohitMe’s suggestion, I make a test and it seems to achieve your requirement.

11.png

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

View solution in original post

3 REPLIES 3
rohitMe Regular Visitor
Regular Visitor

Re: Fill down collumn information based on condition

Hi @iHickUp

 

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

Community Support Team
Community Support Team

Re: Fill down collumn information based on condition

Hi @iHickUp

Thanks to rohitMe’s suggestion, I make a test and it seems to achieve your requirement.

11.png

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

View solution in original post

iHickUp Regular Visitor
Regular Visitor

Re: Fill down collumn information based on condition

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!


Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors