cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justlogmein
Helper III
Helper III

How to get the value from a previous row when the column is text or boolean?

I normally use the below DAX to retrieve a value from a pevious row, which works fine if the MAX value is a number. But what do I do if I am trying to get a value from a column that is a text or boolean type?

=
CALCULATE
	(
	MAX
		(
		'Sample Data'[Sample Taken]
		),
	FILTER
		(
		'Sample Data',
		'Sample Data'[Sample Date] < EARLIER
			(
			'Sample Data'[Sample Date]
			)
		&& 'Sample Data'[Unit Number] = EARLIER
			(
			'Sample Data'[Unit Number]
			)
		)
	)

 

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @justlogmein ,

 

Has this question been solved? If you have solved it, you can post your solution here. If any of the responses were helpful to you, please consider marking them as solutions, which will help more users find similar problems faster. If the question is not solved, please feel free to let us know. Thanks in advance!

 

Best Regards,
Gao

Community Support Team

tamerj1
Super User
Super User

Hi @justlogmein 

please use

=
VAR CurrentDate = 'Sample Data'[Sample Date]
VAR CurrentUnitTable =
    CALCULATETABLE (
        'Sample Data',
        ALLEXCEPT ( 'Sample Data', 'Sample Data'[Unit Number] )
    )
VAR DatesBeforeTable =
    FILTER ( CurrentUnitTable, 'Sample Data'[Sample Date] < CurrentDate )
VAR PreviousDate =
    MAXX ( DatesBeforeTable, 'Sample Data'[Sample Date] )
RETURN
    MAXX (
        FILTER ( DatesBeforeTable, 'Sample Data'[Sample Date] = PreviousDate ),
        'Sample Data'[Sample Taken]
    )

Unfortunately I get an error.

justlogmein_0-1655031174029.png

 

@justlogmein 

Please try

=
VAR CurrentDate = 'Sample Data'[Sample Date]
VAR CurrentUnitTable =
    CALCULATETABLE (
        'Sample Data',
        ALLEXCEPT ( 'Sample Data', 'Sample Data'[Unit Number] )
    )
VAR DatesBeforeTable =
    FILTER ( CurrentUnitTable, 'Sample Data'[Sample Date] < CurrentDate )
VAR PreviousDate =
    MAXX ( DatesBeforeTable, 'Sample Data'[Sample Date] )
RETURN
    SELECTCOLUMNS (
        FILTER ( DatesBeforeTable, 'Sample Data'[Sample Date] = PreviousDate ),
        "@Taken", 'Sample Data'[Sample Taken]
    )

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors