The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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] ) )
@Anonymous, Try a measure like
Measure = Var _tab= SUMMARIZE('Table', 'Table'[CistomerId],"_1" ,Max('Table'[SalesID]))
return COUNTROWS(SUMMARIZE(_tab,[_1]))
User | Count |
---|---|
78 | |
74 | |
43 | |
32 | |
28 |
User | Count |
---|---|
104 | |
93 | |
51 | |
51 | |
46 |