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:

     

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