Compare Rows to Return Value

[rrhutch]

I am a former Tableau user and I trying to figure out how to recrease a cacluated field from Tableau with DAX in PowerBI. The formula I was using in Tableau was:

If {fixed [Academic Year], [Academic Term], [Program], [Degree], [People Code Id], [Stage App Decision], [Create Time]: MAX([DATE + TIME])} = {fixed [Academic Year], [Academic Term], [Program], [Degree], [People Code Id]: MAX([DATE + TIME])} THEN [Stage App Decision] ELSE NULL END

This formula is comparing records based on the max date and context of certain fields. I am thinking utlizing the LASTDATE function seems right, but I am not certain how to set the context. I assume it is using the FILTER function, but I am not sure.

Any thoughts on how to accomplish this in DAX?

[Anonymous]

Hi @rrhutch,

Could you please share raw data of your tables and post expected result here?

Thanks,
Lydia Zhang

[rrhutch]

Academic Year	Academic Term	Program	Degree	People Code ID	Stage App Decision	Create Time	Date	Time
2016	Fall	Grad	Masters	5555666	Accepted	5:56pm	5/15/2016	2:37PM
2016	Fall	Grad	Masters	5555666	Pending	5:56pm	5/15/2016	8:59AM

Above is the dataset after the user filters the data with a date slicer. This is when the measure above is applied.

The result would create the following:

Academic Year	Academic Term	Program	Degree	People Code ID	Stage App Decision	Create Time	Date	Time	Result
2016	Fall	Grad	Masters	5555666	Accepted	5:56pm	5/15/2016	2:37PM	Accepted
2016	Fall	Grad	Masters	5555666	Pending	5:56pm	5/15/2016	8:59AM	Null

Basically it is determining the max row of the filtered dataset and giving me a field off of which I can filter the visualization since I only want to include the max row based on a combo of the Date and Time field.

[Anonymous · Accepted solution]

Hi @rrhutch,

Based on above sample data, firstly, create new column in Power BI Desktop using the formula below, then change the data type of newfield to Date/Time  using Data Type option under Modeling ribbon.

newfield = CONCATENATE(Table[Date],CONCATENATE(" ",Table[Time]))

Secondly, create a new column using the following formula.

Result = IF(Table[newfield]=MAX(Table[newfield]), Table[Stage App Decision],"Null")

Thirdly, create a table visual, see my example.

Thanks,
Lydia Zhang