cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rrhutch
Helper II
Helper II

Compare Rows to Return Value

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?

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

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Microsoft
Microsoft

Hi @rrhutch,


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

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Academic YearAcademic TermProgramDegreePeople Code IDStage App DecisionCreate TimeDateTime
2016FallGradMasters5555666Accepted5:56pm5/15/20162:37PM
2016FallGradMasters5555666Pending5:56pm5/15/20168: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 YearAcademic TermProgramDegreePeople Code IDStage App DecisionCreate TimeDateTimeResult
2016FallGradMasters5555666Accepted5:56pm5/15/20162:37PMAccepted
2016FallGradMasters5555666Pending5:56pm5/15/20168:59AMNull

 

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.

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

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.