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
Nicolai
Frequent Visitor

How to select a sub-set of rows to do a min()

Hi,
I have somewhat of a challenge I can't quite figure out.
I have an SQL table full of production data information and some OK counters.
The OK counters are not normalized, meaning they carry on the counter from the previous order.
I would like to add a column to my dataset, where I normalize this data.
How I imagined I would do it, was simply to subtract the MIN value, for that production line.
Unfortunately I can't figure out how to select a subset of the rows, based on the production line's Id.
If I just do a min(), I will select across multiple production-lines, meaning the min will only be valid for one of them.

So my table is basically:

ProductionLineId, OrderNo, Timestamp, OkCounter

Where I'd like to add a NormalizedOk.

How should I go ahead with achieving this?

I hope it is clear what I'm trying to achieve. Please ask any questions that come up.

1 REPLY 1
quratzafar
Frequent Visitor

This is how I would try to solve the problem,

Min/Production ID = Summarize(<YourTableName>, ProductionID, min (OK number))

This will generate a table that conatins the minimum for each production id. Then you can join this table with the original and create a new calculated column where you subtract i.e 

Normalized number = ok number - min number 

 

Let me know if this helps 

 

 

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.

Top Solution Authors