MINIFs equival- Filter grouped rows by which one has the lowest / minimum value in a specific column

[rtb]

I'm attempting to narrow a query's results down to only the row that has the lowest number in the OPRNUM column. This string works, but any attempt to narrow with a MIN does not. Group by would be for any TRANSREFID, and I've tried with many combinations of Grouped By columns. 

= Sql.Database("---", "---", [Query="SELECT TRANSREFID, OPRNUM, OPRID, OPRFINISHED, S.CUSTACCOUNT, S.ITEMID, S.INVENTREFID, S.LINENUM, S.NAME, S.PROJID, S.SALESID, S.SALESQTY, S.SHIPPINGDATECONFIRMED, S.SHIPPINGDATEREQUESTED, C.COMPANYCHAINID FROM PRODROUTETRANS P#(lf)LEFT JOIN SALESLINE S ON S.INVENTREFID = P.TRANSREFID#(lf)LEFT JOIN CUSTTABLE C ON C.ACCOUNTNUM = S.CUSTACCOUNT#(lf)WHERE P.DATAAREAID = '141' AND OPRFINISHED = 0"])

[v-saisrao-msft]

Hi @rtb,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @rtb,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @rtb, 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi  @rtb,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Thank you @AlexisOlson, for your insights.

Try using a window function such as ROW_NUMBER () in a Common Table Expression (CTE). This function assigns a sequential rank to rows within each TRANSREFID group, ordered by OPRNUM in ascending order. By filtering the results to include only rows where this rank equals one, you effectively select the full row that contains the smallest OPRNUM per group. 

This method preserves all related columns without complicated joins or grouping, and it performs efficiently. Consider this logic inside a SQL view, which you can then connect directly from Power BI. 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 

Thank you. 

[AlexisOlson]

Is this the type of logic you're after?

powerbi - Return top value ordered by another column - Stack Overflow

[rtb]

Close, but not quite. If I interpret that link correctly, it only brings the top record. I need all unique TRANSREFIDs (which are not unique) and the lowest OPRNUM value each one has. It would be akin to this table below, and the result would be

Alexis 3

rtb 2

Name / INVENTREFID	Tacos Eaten / OPRNUM
Alexis	3
rtb	4
Alexis	5
rtb	2
rtb	3

[AlexisOlson]

It looks like @v-saisrao-msft is suggeting something like this:

let
    Source = Sql.Database(
        "<server>",
        "<database>",
        [
            Query = "
                WITH RankedOps AS (
                    SELECT
                        P.TRANSREFID,
                        P.OPRNUM,
                        P.OPRID,
                        P.OPRFINISHED,
                        S.CUSTACCOUNT,
                        S.ITEMID,
                        S.INVENTREFID,
                        S.LINENUM,
                        S.NAME,
                        S.PROJID,
                        S.SALESID,
                        S.SALESQTY,
                        S.SHIPPINGDATECONFIRMED,
                        S.SHIPPINGDATEREQUESTED,
                        C.COMPANYCHAINID,
                        ROW_NUMBER() OVER (
                            PARTITION BY P.TRANSREFID
                            ORDER BY P.OPRNUM ASC
                        ) AS rn
                    FROM
                        PRODROUTETRANS AS P
                        LEFT JOIN SALESLINE AS S
                            ON S.INVENTREFID = P.TRANSREFID
                        LEFT JOIN CUSTTABLE AS C
                            ON C.ACCOUNTNUM = S.CUSTACCOUNT
                    WHERE
                        P.DATAAREAID     = '141'
                        AND P.OPRFINISHED = 0
                )
                SELECT
                    TRANSREFID,
                    OPRNUM,
                    OPRID,
                    OPRFINISHED,
                    CUSTACCOUNT,
                    ITEMID,
                    INVENTREFID,
                    LINENUM,
                    NAME,
                    PROJID,
                    SALESID,
                    SALESQTY,
                    SHIPPINGDATECONFIRMED,
                    SHIPPINGDATEREQUESTED,
                    COMPANYCHAINID
                FROM
                    RankedOps
                WHERE
                    rn = 1
            "
        ]
    )
in
    Source