Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.