Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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])))
=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]))
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')
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])))