Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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])))