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
    11 months 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
11 months 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:

 

mohsin-raza's avatar
mohsin-raza
Helper III
11 months ago

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..