Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | Date | Mileage for each day | Total Lifetime Miles | Last Date in Service (the correct forumula for Measure should output the following information for each vehicle) | ||
1 | 1/1/2017 | 100 | 100 | 1/4/2017 | ||
1 | 1/2/2017 | 200 | 300 | 1/4/2017 | ||
1 | 1/3/2017 | 300 | 600 | 1/4/2017 | ||
1 | 1/4/2017 | 400 | 1000 | 1/4/2017 | ||
2 | 1/1/2017 | 101 | 101 | 1/3/2017 | ||
2 | 1/2/2017 | 102 | 203 | 1/3/2017 | ||
2 | 1/3/2017 | 103 | 306 | 1/3/2017 | ||
2 | 1/4/2017 | 0 | 306 | 1/3/2017 | ||
3 | 1/1/2017 | 201 | 201 | 1/2/2017 | ||
3 | 1/2/2017 | 202 | 403 | 1/2/2017 | ||
3 | 1/3/2017 | 0 | 403 | 1/2/2017 | ||
3 | 1/4/2017 | 0 | 403 | 1/2/2017 | ||
4 | 1/1/2017 | 301 | 301 | 1/1/2017 | ||
4 | 1/2/2017 | 0 | 301 | 1/1/2017 | ||
4 | 1/3/2017 | 0 | 301 | 1/1/2017 | ||
4 | 1/4/2017 | 0 | 301 | 1/1/2017 |
Solved! Go to 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))
Hi,
Here's a calculated column formula i wrote
=CALCULATE(MAX([Date]),FILTER(Table1,[Vehicle]=EARLIER([Vehicle])&&[Mileage for each day]>0))
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))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |