Forum Discussion

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

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

Super User

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

mohsin-raza

Helper III

11 months ago

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

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

Forum Discussion

Need halp in solving copying row value in table

Related content

Copy Values

Copy Job vs Copy Activity

Copy paths

Copy/Duplicate Value

Copy Job OVERWRITE