Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to Solution.
@Anonymous, Try a measure like
Measure = Var _tab= SUMMARIZE('Table', 'Table'[CistomerId],"_1" ,Max('Table'[SalesID]))
return COUNTROWS(SUMMARIZE(_tab,[_1]))
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] ) )
Many thanks to both of you! This works brilliant!
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] ) )
User | Count |
---|---|
82 | |
78 | |
67 | |
46 | |
45 |
User | Count |
---|---|
105 | |
44 | |
39 | |
39 | |
39 |