Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |