Forum Discussion
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.
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
Anonymous, Try a measure like
Measure = Var _tab= SUMMARIZE('Table', 'Table'[CistomerId],"_1" ,Max('Table'[SalesID]))
return COUNTROWS(SUMMARIZE(_tab,[_1]))
3 Replies
- amitchandak
Super User
Anonymous, Try a measure like
Measure = Var _tab= SUMMARIZE('Table', 'Table'[CistomerId],"_1" ,Max('Table'[SalesID]))
return COUNTROWS(SUMMARIZE(_tab,[_1]))