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
balbuquerque
New Member

DAX Calculation - MAX(if for multiple criteiria - Most recent date with mileage over 1 mile for each

Hello,

Can anyone please help me with the DAX logic I'm trying to formulate for my calculated column. I'm trying to create a new measure in the dataset to calculate the most recent date, where each of the 630 vehicles had mileage over 1 mile.  In other words, I want to know when was the the vehicle run last or what was the most recent date the vehicle has a daily mile over 1 mile.  The table below summarizes what the data looks like and what I want to accomplish.

 

The DAX formula I have is: Measure = MAX(if('TableName'[Miles])>1,'TableName'[Date]))

but it's obviosuly way off. Please help me, thank you.

 

 

A

 

Vehicle

DateMileage for each dayTotal Lifetime MilesLast Date in Service  (the correct forumula for Measure should output the following information for each vehicle)  
11/1/2017      1001001/4/2017  
11/2/2017      2003001/4/2017  
11/3/2017      3006001/4/2017  
11/4/2017      40010001/4/2017  
21/1/2017      1011011/3/2017  
21/2/2017      1022031/3/2017  
21/3/2017      1033061/3/2017  
21/4/2017      03061/3/2017  
31/1/2017      2012011/2/2017  
31/2/2017      2024031/2/2017  
31/3/2017      04031/2/2017  
31/4/2017      04031/2/2017  
41/1/2017      3013011/1/2017  
41/2/2017      03011/1/2017  
41/3/2017      03011/1/2017  
41/4/2017      03011/1/2017  
1 ACCEPTED SOLUTION

Hi @balbuquerque,

I am guessing somewhat here because I don't truly know the structure of your data nor the set-up of the visual your are creating, but please try the below. 

Measure 2 =
      VAR V = MAX(OperatingStatistic[EquipmentNum])
      RETURN
      CALCULATE(MAX(OperatingStatistic[StatDate]),
FILTER(ALL(OperatingStatistic),
OperatingStatistic[EquipmentNum] = V && OperatingStatistic[StatMeter] > 1))

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

 

Here's a calculated column formula i wrote

 

=CALCULATE(MAX([Date]),FILTER(Table1,[Vehicle]=EARLIER([Vehicle])&&[Mileage for each day]>0))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
erik_tarnvik
Solution Specialist
Solution Specialist

VehicleMaxDate = 
VAR V = MAX(Miles[Vehicle])
RETURN
CALCULATE(MAX(Miles[Date]),FILTER(ALL(Miles),Miles[Vehicle] = V && Miles[Milage] > 1))

I tried using the above syntax but failed. The error says my EquipmentNum is incorrect. Please help.

 

Measure 2 =
      VAR V = MAX(OperatingStatistic[StatValue][EquipmentNum])
      RETURN
          CALCULATE(MAX(OperatingStatistic[StatValue][StatDate]),FILTER(ALL(OperatingStatistic[StatValue]),OperatingStatistic[StatValue][EquipmentNum] = V && OperatingStatistic[StatValue][StatMeter] > 1))

Is EquipmentNum a number or a string?

 

If it is a string (contains some kind of text), then you need to wrap value with quotation marks ""

For example:

If string:

OperatingStatistic[EquipmentNum] = "V"

If number:

OperatingStatistic[EquipmentNum] = 5

 

Make sure 

Hi @balbuquerque,

I am guessing somewhat here because I don't truly know the structure of your data nor the set-up of the visual your are creating, but please try the below. 

Measure 2 =
      VAR V = MAX(OperatingStatistic[EquipmentNum])
      RETURN
      CALCULATE(MAX(OperatingStatistic[StatDate]),
FILTER(ALL(OperatingStatistic),
OperatingStatistic[EquipmentNum] = V && OperatingStatistic[StatMeter] > 1))

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.