topic Re: Distinct count of a record set minus what is in another record set in DAX Commands and Tips

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

VeraVB — Wed, 27 Apr 2022 13:01:56 GMT

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?

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

johnt75 — Wed, 27 Apr 2022 14:39:00 GMT

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) )

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

VeraVB — Thu, 28 Apr 2022 05:50:19 GMT

Hi John,

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

kind regards,

Vera