Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I need help, I'm not able to calculate the distinct positions considering the last inspection of the vehicle in the month.
TABLE:
Fleet | Position | Date | Value |
Fleet1 | 695475-XC | 2020-04-05 | 6.8 |
Fleet1 | 695475-XC | 2020-04-16 | 10.2 |
Fleet1 | 324567-KQ | 2020-04-25 | 7.4 |
Fleet1 | 324567-KQ | 2020-04-12 | 15.5 |
Fleet1 | 893765-JD | 2020-04-19 | 8.9 |
Fleet1 | 893765-JD | 2020-04-28 | 5.2 |
FILTERS: Year: 2020 | Month: April
RESULT
Fleet | Total Unique Positions - Over (10) Qty | Total Unique Positions - Between (7-10) Qty | Total Unique Positions - Below (7) Qty |
Fleet1 | 1 | 1 | 1 |
I'll be very grateful if anyone can help me!
Regards,
Alexandre Martins
Solved! Go to Solution.
I was able to prepare a solution after watching the video on the link below:
Calculate The Last Or Max Value Within A Table Of Data Using DAX In Power BI
Solution:
Measure Max Date Position =
VAR CurrentPosition = SELECTEDVALUE(inspections[Position])
RETURN
MAXX(
FILTER( ALLSELECTED(inspections), inspections[Position] = CurrentPosition),
inspections[Date] )
)
Measure Over 10 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] > 10,
FILTER(inspections, inspections[Date] = [Max Date Position])
)
Measure Between 7-10 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] >= 7,
inspections[Value] <= 10,
FILTER(inspections, inspections[Date] = [Max Date Position])
)
Measure Below 7 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] < 7,
FILTER(inspections, inspections[Date] = [Max Date Position])
)
Thank you all,
Regards!
I was able to prepare a solution after watching the video on the link below:
Calculate The Last Or Max Value Within A Table Of Data Using DAX In Power BI
Solution:
Measure Max Date Position =
VAR CurrentPosition = SELECTEDVALUE(inspections[Position])
RETURN
MAXX(
FILTER( ALLSELECTED(inspections), inspections[Position] = CurrentPosition),
inspections[Date] )
)
Measure Over 10 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] > 10,
FILTER(inspections, inspections[Date] = [Max Date Position])
)
Measure Between 7-10 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] >= 7,
inspections[Value] <= 10,
FILTER(inspections, inspections[Date] = [Max Date Position])
)
Measure Below 7 =
CALCULATE(
DISTINCTCOUNT(inspections[Position]),
inspections[Value] < 7,
FILTER(inspections, inspections[Date] = [Max Date Position])
)
Thank you all,
Regards!
Hi @alexandrem79 ,
I have created a calculated table to achieve this:
Table =
SUMMARIZE (
'inspections',
'inspections'[Fleet],
'inspections'[Position],
"last date", MAX ( 'inspections'[Date] ),
"Value",
VAR position = [Position]
RETURN
CALCULATE (
SUM ( inspections[Value] ),
FILTER (
ALL ( inspections ),
inspections[Position] = position
&& inspections[Date]
= CALCULATE (
MAX ( inspections[Date] ),
FILTER ( ALL ( inspections ), inspections[Position] = position )
)
)
)
)
Three measures to calculate conditional totals:
Total Unique Positions -Over (10) Qty =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Value] ), 'Table'[Value] > 10 )
Total Unique Positions - Between (7-10) Qty =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Value] ),
'Table'[Value] >= 7
&& 'Table'[Value] <= 10
)
Total Unique Positions - Below (7) Qty =
CALCULATE ( DISTINCTCOUNT ( 'Table'[Value] ), 'Table'[Value] < 7 )
Sample file is attached that hopes to help you, please check and try it: Calculate the distinct positions considering the last inspection in the month the vehicle.pbix
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
How have you got those answers - 1 for each? Shouldn't the answer of Total Unique Positions - Over (10) Qty be 2?
Thanks for your answer!
I need the value of the most recent inspection of the month:
Fleet | Position | Date | Value |
Fleet1 | 695475-XC | 2020-04-16 | 10.2 |
Fleet | Total Unique Positions - Over (10) Qty | Total Unique Positions - Between (7-10) Qty | Total Unique Positions - Below (7) Qty |
Fleet1 | 1 | 0 | 0 |
I tried that, but it did not work:
Total Unique Positions - Over (10) Qty =
@alexandrem79 , Please find the attached solution file. You may have to change no of groupbys in summarize as per need.
The measure is the latest. and measure 2 is the latest in month
Hi,
The most recent inspection for the month is 16 April 2020 for 695475-XC and 12 April 2020 for 324567-KQ. The value for both such instances is > 10 so thae answer should be 2. You may download my PBI file from here.
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |