Find the first value by category - dax

Question

I'm trying to find out how to choose first and last date and quantity for each category, here ORDER_LINE_RELEASE_NO.

ORDER_LINE_RELEASE_NO	WANTED_DATE_OLD	WANTED_DATE_NEW	BUY_QTY_DUE_OLD	BUY_QTY_DUE_NEW
49562_1_9		27.01.2022		1
49562_1_9	27.01.2022	27.01.2022	1	2660
50081_1_1		31.01.2022		6
50081_1_1	31.01.2022	31.03.2022	6	6
50081_1_1	31.03.2022	31.03.2022	6	1210
50084_1_1		10.02.2022		1
50084_1_1	10.02.2022	10.03.2022	1	1
50084_1_1	10.03.2022	10.06.2022	1	1
50084_2_1		10.02.2022		60
50084_2_1	10.02.2022	08.04.2022	60	60
52370_1_1		13.05.2022		3000
52370_1_1	13.05.2022	13.05.2022	3000	2000

In this original table I have the same ORDER_LINE_RELEASE_NO in more rows and I would like to "summarize" it like in the second table here:

ORDER_LINE_RELEASE_NO	FIRST_DATE	LAST_DATE	ORIGINAL_QTY	LAST_WANTED_QTY
49562_1_9	27.01.2022	27.01.2022	1	2660
50081_1_1	31.01.2022	31.03.2022	6	1210
50084_1_1	10.02.2022	10.06.2022	1	1
50084_2_1	10.02.2022	08.04.2022	60	60
52370_1_1	13.05.2022	13.05.2022	3000	2000

So basically in the column FIRST_DATE we have the first value from column WANTED_DATE_NEW (for each category), in LAST_DATE the last value from WANTED_DATE_NEW, in ORIGINAL_QTY is the first value from BUY_QTY_DUE_NEW and in LAST_WANTED_QTY we have the last value from BUY_QTY_DUE_NEW.

I tried to use FIRSTNONBLANK and LASTNONBLANK functions, but they only work fot dates, not for all quantity - for example for 52370_1_1 quantity. My code in creating new table from another in powerBI was:

PURCH_ORD_LINE_UNIQUE =
ADDCOLUMNS (
    DISTINCT ( PURCH_ORD_LINE_ARCH[ORDER_LINE_RELEASE_NO] ),
    "FIRST_DATE",
        CALCULATE (
            FIRSTNONBLANK (
                PURCH_ORD_LINE_ARCH[WANTED_DATE_NEW],
                PURCH_ORD_LINE_ARCH[WANTED_DATE_NEW]
            ),
            ALLEXCEPT ( PURCH_ORD_LINE_ARCH, PURCH_ORD_LINE_ARCH[ORDER_LINE_RELEASE_NO] )
        ),
    "LAST_DATE",
        CALCULATE (
            LASTNONBLANK (
                PURCH_ORD_LINE_ARCH[WANTED_DATE_NEW],
                PURCH_ORD_LINE_ARCH[WANTED_DATE_NEW]
            ),
            ALLEXCEPT ( PURCH_ORD_LINE_ARCH, PURCH_ORD_LINE_ARCH[ORDER_LINE_RELEASE_NO] )
        ),
    "ORIGINAL_QTY",
        CALCULATE (
            FIRSTNONBLANK (
                PURCH_ORD_LINE_ARCH[BUY_QTY_DUE_NEW],
                PURCH_ORD_LINE_ARCH[BUY_QTY_DUE_NEW]
            )
        ),
    "LAST_WANTED_QTY",
        CALCULATE (
            LASTNONBLANK (
                PURCH_ORD_LINE_ARCH[BUY_QTY_DUE_NEW],
                PURCH_ORD_LINE_ARCH[BUY_QTY_DUE_NEW]
            )
        )
)

Sorry if my question is too stupid, I'm quite new to DAX and PowerBI. Thanks for any answer. Tomas

Umut K · Answer

here is my solution : (i will share the sample file at the end, so you can easily understand my approach)

First we will add an index column to the table :

#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type)

then, we will add grouped index number column :

Group Ranking =
RANKX (
    FILTER (
        'Table',
        EARLIER ( 'Table'[ORDER_LINE_RELEASE_NO ] ) = 'Table'[ORDER_LINE_RELEASE_NO ]
    ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

finally, we create our table :

Modelling --> New Table

Result Table = 
ADDCOLUMNS (
    SUMMARIZE ( 'Table', 'Table'[ORDER_LINE_RELEASE_NO ] ),
    "FIRST_DATE", CALCULATE ( MIN ( 'Table'[WANTED_DATE_OLD ] ) ),
    "LAST_DATE", CALCULATE ( MAX ( 'Table'[WANTED_DATE_new ] ) ),
    "ORIGINAL_QTY",
        VAR _max =
            CALCULATE (
                MIN ( 'Table'[Group Ranking] ),
                ALLEXCEPT ( 'Table', 'Table'[ORDER_LINE_RELEASE_NO ] ),
                NOT ( ISBLANK ( 'Table'[BUY_QTY_DUE_OLD ] ) )
            )
        RETURN
            CALCULATE (
                FIRSTNONBLANK ( 'Table'[BUY_QTY_DUE_OLD ], 1 ),
                ALLEXCEPT ( 'Table', 'Table'[ORDER_LINE_RELEASE_NO ] ),
                'Table'[Group Ranking] = _max
            ),
    "LAST_WANTED_QTY",
        VAR _max =
            CALCULATE (
                MAX ( 'Table'[Group Ranking] ),
                ALLEXCEPT ( 'Table', 'Table'[ORDER_LINE_RELEASE_NO ] )
            )
        RETURN
            CALCULATE (
                SUM ( 'Table'[BUY_QTY_DUE_NEW] ),
                ALLEXCEPT ( 'Table', 'Table'[ORDER_LINE_RELEASE_NO ] ),
                'Table'[Group Ranking] = _max
            )
)

Here is the sample PBix File for you...

Find the first value by category - dax

1 Answers