Forum Discussion

ArvindJha's avatar
ArvindJha
Helper III
1 year ago
Solved

Fetch data from previous row

Hi Team,   I want to fetch data from previous row and create some if condition based on the values , it should restart for every patient and also be similar to Qlik peek function , thanks Input: ...
Need Help
  • TomMartens's avatar
    TomMartens
    1 year ago

    Hey ArvindJha ,
    I use the below DAX to create a calculated column 

     

    WEIGHT_ = 
var currentPatientID = 'Table'[PATIENTID]
var currentSite = 'Table'[SITE]
var prevDate = 
    maxx(
        SELECTCOLUMNS(
            FILTER(
                WINDOW(
                    1, abs, -1, rel,
                    SUMMARIZE(
                        ALLSELECTED( 'Table' ), 
                        'Table'[PATIENTID],
                        'Table'[SITE],
                        'Table'[DATE],
                        'Table'[WEIGHT]
                    ),
                    ORDERBY( 'Table'[DATE] , ASC),
                    PARTITIONBY( 'Table'[PATIENTID] , 'Table'[SITE])
                ),
                NOT( ISBLANK( 'Table'[WEIGHT] ) )
            ),
            "d" , [DATE]
        )
        , [d]
    )
return
CALCULATE(
    MAX('Table'[WEIGHT]),
    ALL('Table'),
    'Table'[PATIENTID] = currentPatientID,
    'Table'[SITE] = currentSite,
    'Table'[DATE] = prevDate
)

     

    The table looks like this

    Hopefully, this provides what you are looking for.
    If not, read this article: https://www.minceddata.info/2024/03/09/my-favorite-windowing-function-window/


    Regards,

    Tom

PavanLalwani's avatar
PavanLalwani
Resolver II
1 year ago

To fetch data from the previous row for each patient and site (similar to Qlik’s `Peek` function) in Power BI, you can use **DAX** in a calculated column. Here’s how to approach it:

1. **Use the `EARLIER` Function**: Create calculated columns that reference the previous row for `HEIGHT` and `WEIGHT`.

### Example DAX:
```DAX
Previous_Height =
IF(
ISBLANK('Table'[Height]),
CALCULATE(MAX('Table'[Height]),
FILTER('Table',
'Table'[PATIENTID] = EARLIER('Table'[PATIENTID]) &&
'Table'[SITE] = EARLIER('Table'[SITE]) &&
'Table'[DATE] < EARLIER('Table'[DATE])
)
),
'Table'[Height]
)

Previous_Weight =
IF(
ISBLANK('Table'[Weight]),
CALCULATE(MAX('Table'[Weight]),
FILTER('Table',
'Table'[PATIENTID] = EARLIER('Table'[PATIENTID]) &&
'Table'[SITE] = EARLIER('Table'[SITE]) &&
'Table'[DATE] < EARLIER('Table'[DATE])
)
),
'Table'[Weight]
)
```

This logic fetches the previous `Height` and `Weight` for the same patient and site when current values are blank.