Forum Discussion

mohsin-raza's avatar
mohsin-raza
Helper III
1 year ago
Solved

Need halp in solving copying row value in table

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 l...
Need Help
  • MarkLaf's avatar
    MarkLaf
    1 year ago

    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:

     

MarkLaf's avatar
MarkLaf
Super User
1 year ago

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's avatar
mohsin-raza
Helper III
1 year ago

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's avatar
    MarkLaf
    Super User
    1 year ago

    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's avatar
      mohsin-raza
      Helper III
      1 year ago

      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's avatar
        MarkLaf
        Super User
        1 year ago

        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: