Struggling with not in subqueries

[PowerBIPilgrim]

Hi Team,

I have the following SQL Query: 

select,count(AccountID)
from Table
where [Date] = '2018-01-13' and Attribute = 1
and AccountID not in (select AccountID
from table
where date = '2017-12-13')

In my PowerBI schema, Date is joined to my Calendar table, so in the DAX I've written this:

measure =

CALCULATE(
count(Table[AccountID]),
FILTER(Table,Table[attribute] = 1),
FILTER(Table, NOT(Table[AccountID] in VALUES(Table[AccountID]) && PREVIOUSMONTH(Calendar[Date])
)))

And I'm pulling my hair out trying multiple combinations to replicate the SQL query in DAX, I feel the above comes closest, but I've fundamentally missed something

[v-alq-msft]

Hi, @PowerBIPilgrim 

I'd like to suggest you try the following measure to see if it helps.

measure =
VAR tab =
    CALCULATETABLE (
        DISTINCT ( Table[AccountID] ),
        FILTER ( ALL ( Table ), [Date] = DATE ( 2017, 12, 13 ) )
    )
RETURN
    CALCULATE (
        COUNT ( Table[AccountID] ),
        FILTER (
            ALL ( Table ),
            Table[attribute] = 1
                && Table[Date] = DATE ( 2018, 1, 13 )
                && NOT ( Table[AccountID] IN tab )
        )
    )

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PowerBIPilgrim , Try formula like

measure =
var _tab = selectcolumn(filter(Table, Table[Date] =date(2017,12,13)),"Account",Table[Account])

CALCULATE(
count(Table[AccountID]),
FILTER(Table,Table[attribute] = 1 && Table[Date] =date(2018,01,13) && Table[Account] in _tab )

)

or

measure =
var _tab = selectcolumn(filter(Table, datesmtd(Date[Date])),"Account",Table[Account])

CALCULATE(
count(Table[AccountID]),
FILTER(Table,Table[attribute] = 1 && Table[Account] in _tab )
,datesmtd(dateadd(Date[Date],-1,month))
)

[AllisonKennedy]

What are you trying to do in words, rather than SQL query? Looks like you're trying to find the new accounts that weren't in the previous month? Are you wanting this to be dynamic from month to month? The SQL focuses on 1 day only, but your DAX looks at an entire month. 

Start with this blog and see if it's close to what you want, then come back with some questions and more info on what does and doesn't work from that blog and what further questions you have: https://blog.enterprisedna.co/new-vs-existing-customers-advanced-analytics-w-dax/