Need halp in solving copying row value in table

[mohsin-raza]

Hi.

I need  help in write DAX  to create column base on the condition for example . If columnA="500" and columnB="S" then copy value from abov  row as shown in Result (new col) other wise leave it blank.

With thanks ! 

Mohsin

[v-tsaipranay]

Hi @mohsin-raza ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @mohsin-raza ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay]

Hi @mohsin-raza ,

Thank you for reaching out to the Microsoft fabric community forum and I appreciate the helpful guidance already provided by @ChielFaber .

Could you please confirm if the issue has been resolved. I wanted to check if you had the opportunity to review the information provided by @ChielFaber  . Please feel free to contact us if you have any further questions.

Thank you.

[mohsin-raza]

@v-tsaipranay Thanks for your kind mail. 

I am still struggling with same problem . I needs it in as calculated column and over 4 years of data .

regards

[mohsin-raza]

I try to clear the problem with more better way 

I want to copy from pervious date value project leader and task based on the condition if project="900" and artical name ="fever" then copy pervious project leader value and task . 

[MarkLaf]

Here is a solution that I think is following your requirements.

Here are test data I used, pulled from your snips but with an extra project to show how we resolve for when multiple projects fall on the latest previous date. Also, note that this only works if your Date column is of Date data type (or something that sorts appropriately).

Data

Date	Name	Project	ArticleName	Project Leader	Task
1/12/2022	Martin	201690	Ordinarie	Andersson	renovation
1/13/2022	Martin	201690	Ordinarie	Andersson	renovation
1/14/2022	Martin	201690	Ordinarie	Andersson	renovation
1/17/2022	Martin	201690	Ordinarie	Andersson	renovation
1/17/2022	Martin	XXX	YYY	AAA	BBB
1/18/2022	Martin	900	fever
1/19/2022	Martin	900	fever
1/20/2022	Martin	900	fever
1/21/2022	Martin	900	fever

DAX for columns

Project Leader Filled = 
VAR _thisRank = RANK( ORDERBY( Data[Date], ASC ), PARTITIONBY( Data[Project] ) )
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Project], Data[Project Leader], Data[Task] ), 
    Data[Project Leader] <> BLANK() && Data[Task] <> BLANK() // assuming you want non-blank but remove otherwise
    && Data[ArticleName] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Project], ASC // if this were DESC, then result would be "AAA"
    ) 
)
RETURN
IF(
    Data[Project] = "900" && Data[ArticleName] = "fever" && _thisRank = 1,
    CALCULATE( VALUES( Data[Project Leader] ), _validPrevious, REMOVEFILTERS( Data ) ),
    Data[Project Leader]
)

Task Filled = 
VAR _thisRank = RANK( ORDERBY( Data[Date], ASC ), PARTITIONBY( Data[Project] ) )
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Project], Data[Project Leader], Data[Task] ), 
    Data[Project Leader] <> BLANK() && Data[Task] <> BLANK() // assuming you want non-blank but remove otherwise
    && Data[ArticleName] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Project], ASC // if this were DESC, then result would be "BBB"
    ) 
)
RETURN
IF(
    Data[Project] = "900" && Data[ArticleName] = "fever" && _thisRank = 1,
    CALCULATE( VALUES( Data[Task] ), _validPrevious, REMOVEFILTERS( Data ) ),
    Data[Task]
)

Output

[mohsin-raza]

@MarkLaf 

I am very much thank full you effort and reply. Can you see above the excel sheet I share .? I do not want the Andersson or renovation to inserted but AAA and BBB be inserted on condition "900" and "fever" and on just "january 18,2022" not on january 19 or 20 .

when I try the test your code .It is not inserting pervious date coumn value.

Can you please modify the  above code?

With lot of regards.

[MarkLaf]

Got it. It's perhaps a bit hidden, but this is an easy fix that I called out in the comments of my DAX:

Here is the code again with this quick tweak for easy reference:

Project Leader Filled = 
VAR _thisRank = RANK( ORDERBY( Data[Date], ASC ), PARTITIONBY( Data[Project] ) )
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Project], Data[Project Leader], Data[Task] ), 
    Data[Project Leader] <> BLANK() && Data[Task] <> BLANK() // assuming you want non-blank but remove otherwise
    && Data[ArticleName] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Project], DESC // changed to DESC so we get "AAA"
    ) 
)
RETURN
IF(
    Data[Project] = "900" && Data[ArticleName] = "fever" && _thisRank = 1,
    CALCULATE( VALUES( Data[Project Leader] ), _validPrevious, REMOVEFILTERS( Data ) ),
    Data[Project Leader]
)

Task Filled = 
VAR _thisRank = RANK( ORDERBY( Data[Date], ASC ), PARTITIONBY( Data[Project] ) )
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Project], Data[Project Leader], Data[Task] ), 
    Data[Project Leader] <> BLANK() && Data[Task] <> BLANK() // assuming you want non-blank but remove otherwise
    && Data[ArticleName] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Project], DESC // changed to DESC so we get "BBB"
    ) 
)
RETURN
IF(
    Data[Project] = "900" && Data[ArticleName] = "fever" && _thisRank = 1,
    CALCULATE( VALUES( Data[Task] ), _validPrevious, REMOVEFILTERS( Data ) ),
    Data[Task]
)

Note, when multiple projects fall on the most recent previous project date, we'll select the one that is last when sorting project name alphanumerically.

Imporant note. This is not just getting the project lowest down in the visual or data. DAX does not have a sense of "original order" - when order of rows matters, we must define it ourselves via some column or expression depending on function. E.g., if we actually want latest project ID  (rather than last alphanumeric project name), we would have to switch out Data[Project] for Data[Project].

[mohsin-raza]

@MarkLaf  Thanks again. I had the same discussion on this data during this problem. 

I tried again now even a portion of same dataset and your purposed solution . Can you please see?

Not inserting the above values of 17 january 2022. 

with regards

[MarkLaf]

If you recall from the original DAX, part of how we determine if we should add a new value is checking wether the RANK of the row (ordered: Data[Date], ASC | grouped by: Data[Projectnummer]) is 1 (ie, when sorting by date and grouping by project, it is the first row).

One of the benefits of using variables (VAR) is that it helps with troubleshooting. When we comment out our old IF function and just check _thisRank:

Project Leader Filled = 
VAR _thisRank = RANK( 
    ORDERBY( Data[Date], ASC ), 
    PARTITIONBY( Data[Projectnummer] ) 
)
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Projectnummer], Data[projectleader], Data[Projectcategory] ), 
    Data[projectleader] <> BLANK() && Data[Projectcategory] <> BLANK() 
    && Data[Articlanaame] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Projectnummer], DESC
    ) 
)
RETURN
// IF(
//     Data[Projectnummer] = "900" && Data[Articlanaame] = "fever" && _thisRank = 1,
//     CALCULATE( VALUES( Data[projectleader] ), _validPrevious, REMOVEFILTERS( Data ) ),
//     Data[projectleader]
// )
_thisRank

We can see the problem:

In the original sample, there was only one grouping of "900" so I set up the code just grouping by the project number. But that no longer works as there are multiple groups, so, as is, the numbering just continues and our target row's _thisRank is 5, not 1.

The fix is to update our RANK to also partition by article name:

Project Leader Filled = 
VAR _thisRank = RANK( 
    ORDERBY( Data[Date], ASC ), 
    PARTITIONBY( Data[Projectnummer], Data[Articlanaame] )  // <-- added article name to grouping
)
// <excluding all other DAX for this example
RETURN
_thisRank

Now, if we revert back to our old IF function using updated _thisRank:

Project Leader Filled = 
VAR _thisRank = RANK( 
    ORDERBY( Data[Date], ASC ), 
    PARTITIONBY( Data[Projectnummer], Data[Articlanaame] )  // <-- added article name to grouping
)
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Projectnummer], Data[projectleader], Data[Projectcategory] ), 
    Data[projectleader] <> BLANK() && Data[Projectcategory] <> BLANK() 
    && Data[Articlanaame] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Projectnummer], DESC
    ) 
)
RETURN
IF(
    Data[Projectnummer] = "900" && Data[Articlanaame] = "fever" && _thisRank = 1,
    CALCULATE( VALUES( Data[projectleader] ), _validPrevious, REMOVEFILTERS( Data ) ),
    Data[projectleader]
)

Category Filled = 
VAR _thisRank = RANK( 
    ORDERBY( Data[Date], ASC ), 
    PARTITIONBY( Data[Projectnummer], Data[Articlanaame] )  // <-- added article name to grouping
)
VAR _thisDate = Data[Date]
VAR _validRows = FILTER( 
    ALL( Data[Date], Data[Projectnummer], Data[projectleader], Data[Projectcategory] ), 
    Data[projectleader] <> BLANK() && Data[Projectcategory] <> BLANK() 
    && Data[Articlanaame] <> "900" && Data[Date] < _thisDate 
)
VAR _validPrevious = INDEX( 
    1, _validRows, ORDERBY( 
        Data[Date], DESC,
        Data[Projectnummer], DESC
    ) 
)
RETURN
IF(
    Data[Projectnummer] = "900" && Data[Articlanaame] = "fever" && _thisRank = 1,
    CALCULATE( VALUES( Data[Projectcategory] ), _validPrevious, REMOVEFILTERS( Data ) ),
    Data[Projectcategory]
)

Then we get the desired output:

[mohsin-raza]

Again Thanks A lot  MakrLaf for your kind explaination and solution. It works very fine for the first group . for example .

but I check down . I find the same problem 

He reported may be on same days. Again down 

Again.

It may be there is fail rapporting ? One can be have a "fever" and "work for short time" on the same date? or there is  date gap  in rapporting "fever" for example . one works till friday on one wekk  and become "sick" on Monday second week .This is my observation. Now how DAX thinks.

regards.

[MarkLaf · Accepted solution]

Different approach using offset. Looks at previous row in data when sorted by date and pulls from it when current row is 900 and fever.

Project Leader Filled = 
VAR _prevRow  = OFFSET( 
    -1, ALL( Data[date], Data[Projectnummer], Data[Articlanaame] ), 
    ORDERBY( Data[date], ASC ) 
)
VAR _prevProjArticle = SUMMARIZE( _prevRow, Data[Projectnummer], Data[Articlanaame] )
VAR _curIsNewProjArticle = NOT ( Data[Projectnummer], Data[Articlanaame] ) IN _prevProjArticle
RETURN
IF(
    Data[Projectnummer] = "900" && Data[Articlanaame] = "fever",
    IF( 
        _curIsNewProjArticle, 
        CALCULATE( VALUES( Data[projectleader] ), REMOVEFILTERS( Data ), _prevRow ),
        Data[projectleader]
    ),
    Data[projectleader]
)

Category Filled = 
VAR _prevRow  = OFFSET( 
    -1, ALL( Data[date], Data[Projectnummer], Data[Articlanaame] ), 
    ORDERBY( Data[date], ASC ) 
)
VAR _prevProjArticle = SUMMARIZE( _prevRow, Data[Projectnummer], Data[Articlanaame] )
VAR _curIsNewProjArticle = NOT ( Data[Projectnummer], Data[Articlanaame] ) IN _prevProjArticle
RETURN
IF(
    Data[Projectnummer] = "900" && Data[Articlanaame] = "fever",
    IF( 
        _curIsNewProjArticle, 
        CALCULATE( VALUES( Data[Projectcategory] ), REMOVEFILTERS( Data ), _prevRow ),
        Data[Projectcategory]
    ),
    Data[Projectcategory]
)

I stacked on a duplicate of previous sample with rolled forward dates to mimic actual data better (multiple groups of 900,fever)

Data

date	Name	Projectnummer	Articlanaame	projectleader	Projectcategory
1/3/2022	Martin	900	paid holiday
1/4/2022	Martin	900	paid holiday
1/5/2022	Martin	900	paid holiday
1/7/2022	Martin	900	paid holiday
1/10/2022	Martin	201690	work time	patrik	construction
1/11/2022	Martin	201690	work time	patrik	construction
1/12/2022	Martin	201690	temperory work	patrik	construction
1/13/2022	Martin	201690	temperory work	patrik	construction
1/14/2022	Martin	201690	temperory work	patrik	construction
1/17/2022	Martin	201690	temperory work	patrik	construction
1/18/2022	Martin	900	fever
1/19/2022	Martin	900	fever
1/20/2022	Martin	900	fever
1/21/2022	Martin	900	fever
1/24/2022	Martin	900	fever
1/25/2022	Martin	900	fever
1/26/2022	Martin	900	paid holiday
1/27/2022	Martin	900	paid holiday
1/28/2022	Martin	900	paid holiday
1/30/2022	Martin	900	paid holiday
2/2/2022	Martin	201690	work time	patrik	construction
2/3/2022	Martin	201690	work time	patrik	construction
2/4/2022	Martin	201690	temperory work	patrik	construction
2/5/2022	Martin	201690	temperory work	patrik	construction
2/6/2022	Martin	201690	temperory work	patrik	construction
2/9/2022	Martin	201690	temperory work	patrik	construction
2/10/2022	Martin	900	fever
2/11/2022	Martin	900	fever
2/12/2022	Martin	900	fever
2/13/2022	Martin	900	fever
2/16/2022	Martin	900	fever
2/17/2022	Martin	900	fever

Result with new columns:

[mohsin-raza]

@MarkLaf  THANKS ALOT for your kind dedication,commitment and solution. It works and works very fine.  I like to send THANKING flowers🎉🌻🌼 where ever you are !

With REGARDS..

[ChielFaber]

Visual calculations is the way to go. 

I recreated your sample data.

For this to work you need to enable visual calculations in the options menu (preview options).

Put the fields in a matrix. press the three dots on the right upper corner and select new visual calculation: custom

You can then add in a new column calculation: 

project leader

Project leader (filled) =

IF (

    [Project] = "900"

        && [Articalname] = "fever",

    COALESCE ( PREVIOUS([project leader]), [project leader] ),

    [project leader]

)

Task

Task (filled) =

IF (

    [Project] = "900"

        && [Articalname] = "fever",

    COALESCE ( PREVIOUS([task]), [task] ),

    [task]

)

This will give you the desired outcome:

To me this feels the easiest way to accomplish your goal.

Hope this is helpfull.

Regards,

Chiel

[mohsin-raza]

@ChielFaber 

Thanks for your niece help.

I am still not getting the right resuts. PERVIOUS works in visual . but I need this as a calculated column that implements the same law on whole dataset.

[v-tsaipranay]

Hi @mohsin-raza ,

Thanks for clarifying and sharing the error message.

The behavior you’re seeing is expected as PREVIOUS() only works in Visual Calculations, so it can be used in visuals like Matrix or Table, but not as a calculated column in the data model.

If you just want to display the filled values in visuals, Visual Calculation is the right approach. But if you need this as a calculated column in your table, PREVIOUS() won’t work. Instead, you can use Power Query’s Fill Down transformation on the Project leader and Task columns when [Project] = 900 and [Articalname] = "fever", which will persist the values in your data model.

Hope this helps, Please feel free to reachout for any further question.

Thank you.

[mohsin-raza]

@ChielFaber 

Thanks a lot for your kind answer . When I try to implement as an  add column, I get this error 

How to solve this problem?

regards

[ChielFaber]

Check out this video on how to use visual calculations:

https://www.youtube.com/watch?v=eDRgzEVwVEc

Select a matrix visual and add a new column calculation. The previous function looks at the row thats above the current row.

[ChielFaber]

The easiest way will probably be to use the new (preview) visual calculations. 

check out: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview

And especially the previous function from visual calculations: https://learn.microsoft.com/en-us/dax/previous-function-dax 

Hope this is helpfull

Regards,

Chiel