Token Comma Expected Error

[cdavis24]

Hi!  I'm trying to see the records in common between the table ending in '2v' and the one ending in '2v_prior' from the query below.  When I copy/paste working code directly from SQL Server Management Studio to a blank Power BI query screen, Power BI transforms it, and I get the following, which results in a Token Comma Error:

= select * from ro.eria_Details_2v a#(cr)#(lf)where exists (select period, risk_inventory_id from ro.ERIA_Details_2v_Prior b#(cr)#(lf)where a.risk_inventory_id = b.risk_inventory_id)#(cr)#(lf)and a.period = "Q4 2022"

What, within the query, would give me that error?

[wdx223_Daniel]

=let a=List.Buffer(List.Distinct(ro.ERIA_Details_2v_Prior[risk_inventory_id])) in Table.SelectRows(ro.eria_Details_2v,each [period]="Q4 2022" and List.Contains(a,[risk_inventory_id]))

[cdavis24]

So, I'm basically trying to understand how I can write a DAX query which is synonymous with my SQL Server query.  I have 2 tables, and I'm trying to understand how I can count the records which existed on the prior table (the table named ro.ERIA_Details_2v_Prior) but don't exist on the current table (the one named ro.ERIA_Details_2v).  The query below works perfectly in SQL Server, as I'm able to see which records are missing and understand the counts.  I need to replicate it in DAX, but I'm having trouble:

select * from ro.eria_Details_2v_prior a

where not exists (select period, risk_inventory_id, from ro.ERIA_Details_2v b

where a.risk_inventory_id = b.risk_inventory_id and b.period = 'Q4 2022')

[wdx223_Daniel · Accepted solution]

NewTable=CALCULATETABLE(ro.eria_Details_2v_prior,NOT(CONTAINSROW(SUMMARIZE(ro.ERIA_Details_2v,ro.ERIA_Details_2v[period],ro.ERIA_Details_2v[risk_inventory_id]),ro.eria_Details_2v_prior[period],ro.eria_Details_2v_prior[risk_inventory_id])))