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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Anonymous
Not applicable

SUMMARIZECOLUMNS (SQL to DAX)

Hello,

 

DATA: I have two columns CustomerID and SalesID.

 

GOAL:

I made a table visual in the screenshot below displaying CustomerID and Max(SalesID) where SalesID is above -1. That means, the table shows distinct CustomerID with the latest SalesID
Now, I want to do a distinct count on the Max(SalesID) column.

 

 

Natalie123455_0-1709103207848.png

 

HOW I WOULD SOLVE THIS IN SQL:

select CustomerID, max(SalesID)

from [table]

where SalesID > -1

group by CustomerID

 

Based on this table, I would perform a distinct count on the column max (SalesID)

 

BUT IN DAX? 😞

However, I am unsure how to do it in DAX (I tried a lot with SUMMARIZECOLUMNS) and I looked at https://www.sqlbi.com/articles/from-sql-to-dax-filtering-data/ but I couldn't figure it out.

 

Do you have any hints on how to transform the SQL to DAX?

 

Many thanks and best,

Natalie

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous, Try a measure like

 

Measure = Var _tab= SUMMARIZE('Table', 'Table'[CistomerId],"_1" ,Max('Table'[SalesID]))
return COUNTROWS(SUMMARIZE(_tab,[_1]))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Anonymous
Not applicable

Made a minor change to Amit's code to include the filter on SalesID

Measure =
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[CistomerId],
        "_1", CALCULATE ( MAX ( 'Table'[SalesID] ), 'Table'[SalesID] > -1 )
    )
RETURN
    COUNTROWS ( SUMMARIZE ( _tab, [_1] ) )

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Many thanks to both of you! This works brilliant!

Anonymous
Not applicable

Made a minor change to Amit's code to include the filter on SalesID

Measure =
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[CistomerId],
        "_1", CALCULATE ( MAX ( 'Table'[SalesID] ), 'Table'[SalesID] > -1 )
    )
RETURN
    COUNTROWS ( SUMMARIZE ( _tab, [_1] ) )
amitchandak
Super User
Super User

@Anonymous, Try a measure like

 

Measure = Var _tab= SUMMARIZE('Table', 'Table'[CistomerId],"_1" ,Max('Table'[SalesID]))
return COUNTROWS(SUMMARIZE(_tab,[_1]))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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