topic Re: How to find and aggregate the first value in an expression in DAX Commands and Tips

How to find and aggregate the first value in an expression

Hajnalka77 — Fri, 10 Feb 2023 17:34:35 GMT

Hello Guys

I have a column of different numbers, (scantípus) I have to calculate only with 3 numbers (13, 14, 23), but I always need the earliest created number related to the 'csomagszám' column. I have counted them, but I couldn' find how to choose only the earlier created number if more than one number related to the same csomagszám column (related to the same id).

DELIVERY_SZKENEK = CALCULATE(COUNT('Lekérdezés1'[csomagszám]),'Lekérdezés1'[scantípus]=13 || 'Lekérdezés1'[scantípus]=14 || 'Lekérdezés1'[scantípus]=23)

I have these columns

csomagszám Date Time scantípus

id1 2023.02.07 7:42:54 13

id1 2023.02.07 7:42:01 14

id2 2023.02.07 5:00:11 14

id3 2023.02.07 4:18:02 23

Thank you!

Re: How to find and aggregate the first value in an expression

FreemanZ — Fri, 10 Feb 2023 23:57:59 GMT

hi Hajnalka77

try like:

DELIVERY_SZKENEK=

VAR _table =

SUMMARIZE(

Lekérdezés1,

Lekérdezés1[csomagszám],

Lekérdezés1[scantípus]

)

VAR _table2 =

FILTER(

_table,

Lekérdezés1[scantípus] IN {13, 14, 23}

)

RETURN

COUNTROWS(_table2)

Re: How to find and aggregate the first value in an expression

Hajnalka77 — Sun, 12 Feb 2023 21:12:58 GMT

Hello FreemanZ, thanks very much for your time and effort, the result looks much better now.

However I realised there are duplicated rows (duplicated 14, 13 numbers). Can I also filter the duplicated numbers related to Lekérdezés1[csomagszám] column?

Re: How to find and aggregate the first value in an expression

Hajnalka77 — Sun, 12 Feb 2023 22:36:18 GMT

Hello FreemanZ, thanks very much for your time and effort, the result looks much better now.

However I realised there are duplicated rows (duplicated 14, 13 numbers). Can I also filter the duplicated numbers related to Lekérdezés1[csomagszám] column? Or still can I refer always to the earlier (based on date column) and drop the later one?

Re: How to find and aggregate the first value in an expression

FreemanZ — Mon, 13 Feb 2023 02:49:18 GMT

hi Hajnalka77

the duplicates for the column inside SUMMARIZE will be removed. Just try it out.

In case of issue, please provide the dataset enriched enough to reflect your challenge.

Re: How to find and aggregate the first value in an expression

Hajnalka77 — Mon, 13 Feb 2023 06:57:45 GMT

Yes, it was removed, thanks, I have tried. What I meant I want to filter further the result. If one id (csomagszám) has both numbers 13 and 14, I want to count only the earlier created one (based on the time and date column). Thanks.