Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Indicate / Filter Change in Raings

Hey Guys,

 

I've been struggling with this for a while, my data is as follows:

 

BatchItem_IDRating
115
128
132
1412
215
228
233
2412
316
328
332
3410

 

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. 

9 REPLIES 9
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AlB
Super User
Super User

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

 

 

 

Anonymous
Not applicable

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?  

Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

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

Capture.PNG

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.