cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gaven
New Member

Identify NEW clients to a service - from SQL to DAX

Trying to identify clients who have not engaged with a service before (but may have already engaged with another service so are already in the database). Have previously managed this with SQL but not sure how to convert to DAX. 

 

The SQL looks lit this:

 

set @StartDate = '20190401'
set @EndDate = '20200331'
set @service = 'SWRK'

select * from
(
--Total
select a.actiontypeid Service, 'Total Clients Seen' ColumnName, count(distinct a.personid) as [Count],'' as [ ]
from resadm.action A
inner join resadm.Person P on P.PersonID = A.PersonID
where a.dateofaction >= @StartDate
and a.dateofaction <= @EndDate
--and a.status='A'
--and P.RecClassID = 'CCM'
and a.actiontypeid in ('AODAD','AODYTH','WWO','HDX','CMH','TH1')
and a.actiontypeid = @service
group by a.actiontypeid

union

--Total New
select a.actiontypeid Service, 'Total New Clients' ColumnName, count(distinct a.personid) as [Count],'' as [ ]
from resadm.action A
inner join resadm.Person P on P.PersonID = A.PersonID
left join resadm.action A2 on A2.PersonID = A.PersonID and A2.DateOfAction < @StartDate
where a.dateofaction >= @StartDate
and a.dateofaction <= @EndDate
--and a.status='A'
--and P.RecClassID = 'CCM'
and A2.ActionID is null
and a.actiontypeid in ('AODAD','AODYTH','WWO','HDX','CMH','TH1')
and a.actiontypeid = @service
group by a.actiontypeid

 

2 REPLIES 2
amitchandak
Super User
Super User
Greg_Deckler
Super User
Super User

@Gaven - Try this: https://community.powerbi.com/t5/Quick-Measures-Gallery/New-and-Returning-Customers/m-p/168297#M13

 

I also cover New, Returning and Lapsed customers in my book, DAX Cookbook. 

 

Also, since you are a New Member:

Please always first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors