MAX and group by

[hongho2]

Hi,

I am new to DAX and hope you can help me with the syntax below.  Look at SQL command, I need to mimic the query in DAX but keept getting error.  Thanks

SQL command 

select order, line, max(cot) cot

from my table

group by order, line

DAX query

EVALUATE
SUMMARIZECOLUMNS (
'tbl Order'[Order],
'tbl Order'[line],
MAX('tbl Order'[cot])
)
group by
'tbl Order'[Order],
'tbl Order'[line]

[tamerj1]

Hi @hongho2 

please try

EVALUATE
SUMMARIZECOLUMNS (
'tbl Order'[Order],
'tbl Order'[line],
"Cot", CALCULATE ( MAX ( 'tbl Order'[cot] ) )
)

[hongho2]

Thanks for your response. I got error below.

Query (9, 5) Function SUMMARIZECOLUMNS expects a column name as argument number 6.

[tamerj1]

@hongho2 

Would you please paste the formula that you have used

[hongho2]

Can I have "cot", CALCULATE ( MAX ('tbl Order'[cot] ) ) as the filter, can you show me how?

error:

Query (14, 3) Function SUMMARIZECOLUMNS expects a column name as argument number 9.

EVALUATE
SUMMARIZECOLUMNS (
'tbl Order'[ID],
'tbl Order'[Order],
'tbl Order'[line],
"cot", CALCULATE ( MAX ('tbl Order'[cot] ) ),
FILTER ('tbl ID',[ ID] =250 )

[tamerj1]

@hongho2 

Please try

EVALUATE
FILTER (
SUMMARIZECOLUMNS (
'tbl Order'[ID],
'tbl Order'[Order],
'tbl Order'[line],
"cot", CALCULATE ( MAX ( 'tbl Order'[cot] ) )
),
[ ID] = 250
)

[hongho2]

Thank you very much for your promt reponse.  I need to have more filters from the other tables and display amount, but keep getting errors.  

EVALUATE

FILTER (

SUMMARIZECOLUMNS (

'tbl Order'[ID],

'tbl Order'[Order],

'tbl Order'[line],

"cot", CALCULATE ( MAX ( 'tbl Order'[cot] ) )

),

[ ID] = 250,

'tbl type',[Type] <> "air"

'tbl order',[city] = BLANK ()

'tbl order',[dirt] = "No" )

'tbl tem',[email] = "No" )

)

"Order Amount", [Order Amount]

)

[tamerj1]

@hongho2 
No information about your data model so I'm trying to guess

EVALUATE
SUMMARIZE (
    FILTER (
        'tbl Order',
        'tbl Order'[ID] = 250
            && 'tbl order'[city] = BLANK ()
            && 'tbl order'[dirt] = "No"
            && RELATED ( 'tbl type'[Type] ) <> "air"
            && RELATED ( 'tbl item'[email] ) = "No"
    ),
    'tbl Order'[ID],
    'tbl Order'[Order],
    'tbl Order'[line],
    "cot", MAX ( 'tbl Order'[cot] ),
    "Order Amount", [Order Amount]
)