Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey Guys,
I've been struggling with this for a while, my data is as follows:
Batch | Item_ID | Rating |
1 | 1 | 5 |
1 | 2 | 8 |
1 | 3 | 2 |
1 | 4 | 12 |
2 | 1 | 5 |
2 | 2 | 8 |
2 | 3 | 3 |
2 | 4 | 12 |
3 | 1 | 6 |
3 | 2 | 8 |
3 | 3 | 2 |
3 | 4 | 10 |
I need to be able to filter on those items that have changed values per batch, and optionally indicate if the value has gone up or down or remained the same.
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
Hi @Anonymous
By values you mean Ratings I guess?
I quote:
...indicate if the value has gone up or down or remained the same...
What does that mean exactly? gone up or down when? An clarifying example would be useful
Hi @AlB,
Sorry, yes if the rating from batch 1 has increased or decreased for the same object in batch 2 or three. I would need it to be dynamic as you won't always compare sequential batches.
For example in batch 1 ItemID 4 was 12 then in batch 3 ItemID 4 is now a 10 > indicate a decrease of 2.
@Anonymous
How are you planning to indicate which batches are to be compared? Or are you looking to check for changes over all batches together for an ItemID? If so what exactly? The difference between the max and the min ratings for that ItemID?
Most of the times you will only be comparing the earliest and the latest MIN and MAX could work here. So it would normally be 2 batches.
I'm not sure if it will be possible to compare row by row - if you could please help to create a measure that can compare the min(batch) to the Max(batch) for the ratings, just indicate the difference if it went down it would be a negative number if it went up a positive number and no change would then be 0.
Appreciate your assistance. Thank you in advance.
@Anonymous
I'm not quite sure what you mean, but let's see if this is of any help. Create three measures as follows. First these two:
LatestBatchNumber = MAX(Table1[Batch]) EarliestBatchNumber = MIN(Table1[Batch])
and then a third one that makes use of the two previous ones:
RatingDifference = VAR _LatestBatchNumber = [LatestBatchNumber] VAR _EarliestBatchNumber = [EarliestBatchNumber] VAR LatestRating = CALCULATE ( SELECTEDVALUE ( Table1[Rating] ); Table1[Batch] = _LatestBatchNumber ) VAR EarliestRating = CALCULATE ( SELECTEDVALUE ( Table1[Rating] ); Table1[Batch] = _EarliestBatchNumber ) RETURN LatestRating - EarliestRating
You can then use a matrix visual in your report, with Table1[ItemID] in rows and the 3 measures in values. You will thus have, per ItemID, the number of the first batch, the number of the last batch and the difference in ratings between those.
Does that help?
@Anonymous
Another version for the third measure:
RatingDifference_V2 = VAR LatestRating = LOOKUPVALUE ( Table1[Rating]; Table1[Batch]; [LatestBatchNumber]; Table1[ItemID]; SELECTEDVALUE ( Table1[ItemID] ) ) VAR EarliestRating = LOOKUPVALUE ( Table1[Rating]; Table1[Batch]; [EarliestBatchNumber]; Table1[ItemID]; SELECTEDVALUE ( Table1[ItemID] ) ) RETURN LatestRating - EarliestRating
Hi @AlB Thank you so much, dont think I did it right:
RatingDifference =
VAR LatestRating =
LOOKUPVALUE(
RiskRatings[Residual_RiskRating],
RiskRatings[Batch_Key], [MaxBatch],
RiskRatings[UnitRisk_Key], SELECTEDVALUE(RiskRatings[UnitRisk_Key])
)
VAR EarliestRating =
LOOKUPVALUE(
RiskRatings[Residual_RiskRating],
RiskRatings[Batch_Key], [MinBatch],
RiskRatings[UnitRisk_Key], SELECTEDVALUE(RiskRatings[UnitRisk_Key])
)
RETURN
LatestRating - EarliestRating
The total column actually indicates the correct change.
@Anonymous
I ran a quick test on the example data you posted initially. Seems to work. Check it out. Get the file here