Grouping and Min on a table

[Andyr]

Hi,

Coming from a more SQL background I'm still trying to get my head how to do things in power bi

I've got a table with the following in:

PartId, Value, UsesSinceService
1, 65, 5
1, 55, 6
1, 45, 7
1, 35, 11
1, 25, 12
1, 13, 14
2,  65, 2
2, 60, 4
2, 45, 5
2, 44, 6
2, 42, 8

What I need to do is plot this data for each part id with the value on the y-axis and the uses since last service on the x-axis.

However there is a requirement to re align the UsesSinceLastService data to start at 1 for each engine part on the plot - this is so the degradtion curves should roughly alighn with some predicted boundaries.

So I need to find the minimum value for each part id and deduct that from each row relevant to that part id then add 1 so the date ends up like the following:

PartId, Value, UsesSinceService, ReBasedUses
1, 65, 5, 1
1, 55, 6, 2
1, 45, 7, 3
1, 35, 11, 7
1, 25, 12, 8
1, 13, 14, 10
2,  65, 2, 1
2, 60, 4, 3
2, 45, 5, 4
2, 44, 6, 5
2, 42, 8, 7

I did this with a table that only contained 1 part id using the following :

ReBasedUses= 'ComponentParts'[UsesSinceService] - min('ComponentParts'[UsesSinceService]) + 1

However as I add new parts to the table the min is bringing back the min regardless of part id - how is the best way of producing the results I require?

[Andyr · Accepted solution]

So, I achived this by the following method but I'd like to know if this is 'Best Practice'

I created a calculated table with ComponentParts statistics Min, Max and Count of UsesSinceService group'd by PartId

ComponentPartStats = SUMMARIZE('ComponentParts'
      , 'ComponentPart'[PartId]
      , "MinUse", MIN('ComponentPart'[UsesSinceService])
      , "MaxUse", MAX('ComponentPart'[UsesSinceService])
 , "CountOfUses", COUNT('ComponentPart'[UsesSinceService])
)

Then in my ComponentParts table I created a calculated column:

ReUses = 1 + 'ComponentParts'[UsesSinceService] - CALCULATE (
    min(ComponentPartStats[MinUses]),
    FILTER (
        ComponentPartStats,
        ComponentPartStats[PartID] = 'ComponentParts'[PartId]
    ) )

I'd really like to know how to do things 'Properly' so I don't end up with a project that is a total mess or very inefficient!

Cheers