Distinct count of a record set minus what is in another record set

[VeraVB]

Hi,

I'm trying to make DAX formule from something simple in SQL but I don't get it. 

This is the select statement: 

select distinct rav_accountid
from rav_orderhistory
where rav_fiscalyear = 2022
and rav_accountid not in (
(select distinct rav_accountid
from rav_orderhistory
where rav_fiscalyear = 2021))

;

I can count the accounts for 2022 and I can count the ones for 2021, but not the account that exist in 2022 minus the accounts of 2021. 

Is there perhaps a website or an app that can convert SQL to DAX? 

[johnt75 · Accepted solution]

You can use VALUES and EXCEPT, along the lines of

Num new in 2022 =
var accounts2021 = CALCULATETABLE( VALUES('Table'[Account ID]), 'Table'[Year created] = 2021)
var accounts2022 = CALCULATETABLE( VALUES('Table'[Account ID]), 'Table'[Year created] = 2022)
return COUNTROWS( EXCEPT( accounts2022, accounts2021) )

[VeraVB]

Hi John,

Thank you very much, I was actually pretty close, just couldn't exactly nail it. 

kind regards,

Vera