Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hongho2
Frequent Visitor

MAX and group by

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]

7 REPLIES 7
tamerj1
Super User
Super User

Hi @hongho2 

please try

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

Thanks for your response. I got error below.

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

@hongho2 

Would you please paste the formula that you have used

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 )

@hongho2 

Please try

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

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]
)
 

@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]
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors