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:

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

Anonymous 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