Measure to find max values within categories using direct query

[charlineklapu]

Hello,

I am trying to create a new measure, which gives me the max value per order number. 

The table I am working with looks as followed:

Table Name:	Reaktionszeit
Columns:	Auftragsnummer	Reaktionszeit in Tagen	Datum (E)	Datum (S)	Land	Product
	11	5	01.01.2022	05.01.2022	CH	green
	11	10	01.01.2022	10.01.2022	CH	green
	22	6	01.02.2022	06.02.2022	DE	blue
	33	1	01.03.2022	02.03.2022	BE	red
	33	10	01.03.2022	10.03.2022	BE	red
	33	20	01.03.2022	20.03.2022	BE	red

and the desired result looks like this:

Auftragsnummer	Reaktionszeit in Tagen	Datum (E)	Datum (S)	Land	Product
11	10	01.01.2022	10.01.2022	CH	green
22	6	01.02.2022	06.02.2022	DE	blue
33	20	01.03.2022	20.03.2022	BE	red

Important to mention is also that this model is working on direct query mode. Therefore the "normal" Dax formula doesn't work.

I look forard to hear from the dax experts on here, which are willing to help.

I appreciate it!

Have a great evening and best regards from Germany

Charline 

[Syk]

Should be just a max function.. Included a screenshot of your data (I edited headings but same data)

Measure = Max('Table'[Reaktionszeit in Tagen])

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a measure.

Reaktionszeit in Tagen max =
VAR currentreakionszeit =
    SUM ( Reaktionszeit[Reaktionszeit in Tagen] )
RETURN
    IF (
        currentreakionszeit
            = SUMX (
                GROUPBY (
                    FILTER (
                        ALL ( Reaktionszeit ),
                        Reaktionszeit[Auftragsnummer] = MAX ( Reaktionszeit[Auftragsnummer] )
                    ),
                    Reaktionszeit[Auftragsnummer],
                    "@max", MAXX ( CURRENTGROUP (), Reaktionszeit[Reaktionszeit in Tagen] )
                ),
                [@max]
            ),
        currentreakionszeit,
        BLANK ()
    )

[charlineklapu]

thank you, works perfectly!

[v-easonf-msft]

Hi, @charlineklapu '

You can add a new measure like the following and apply it to the Visual Filters pane to filter your data.

Max_ Reaktionszeit in Tagen = 
CALCULATE (
    MAX ( 'Table'[Reaktionszeit in Tagen] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Auftragsnummer] = MAX ( 'Table'[Auftragsnummer] )
    )
)

flag = IF(SELECTEDVALUE('Table'[Reaktionszeit in Tagen])=[Max_ Reaktionszeit in Tagen],1,0)

Best Regards,
Community Support Team _ Eason