SUMMARIZECOLUMNS (SQL to DAX)

[Anonymous]

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.

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

[amitchandak · Accepted solution]

@Anonymous, Try a measure like

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

[Anonymous]

Many thanks to both of you! This works brilliant!

[v-jingzhan-msft · Accepted solution]

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 · Accepted solution]

@Anonymous, Try a measure like

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