Forum Discussion
SQL to DAX
Good morning people,
I would like help to convert a sql command to DAX.
SQL:
SELECT b.numconta,b.codplaco
from contas b
WHERE b.codplaco ='IPN001'
AND b.databert >= '2019-12-15'
AND exists(SELECT 1 from lancamen a WHERE a.numconta=b.numconta and a.codproprio IN ('00902001'))
AND (not exists(SELECT 1 from lancamen a WHERE a.numconta=b.numconta and a.codproprio IN ('00103001'))
OR not exists(SELECT 1 from lancamen a WHERE a.numconta=b.numconta and a.codproprio IN ('00000501')))
MaiconM1 , OK I put this together but I don't have the logic quite right. Can you explain the intent of your SQL in plain language?
PBIX is attached.
Measure = VAR __Table = FILTER( 'contas', MAX(contas[codplaco]) = "IPN001" && MAX(contas[databert]) >= DATE(2019,12,15) && "00902001" IN SELECTCOLUMNS(RELATEDTABLE(lancamen),"__codproprio",[codproprio]) && NOT("00103001" IN SELECTCOLUMNS(RELATEDTABLE(lancamen),"__codproprio",[codproprio])) || NOT("00000501" IN SELECTCOLUMNS(RELATEDTABLE(lancamen),"__codproprio",[codproprio])) ) RETURN IF(COUNTROWS(__Table) > 0,1,BLANK())So close the first time! MaiconM1 PBIX is attached.
Measure = VAR __Table = FILTER( 'contas', MAX(contas[codplaco]) = "IPN001" && MAX(contas[databert]) >= DATE(2019,12,15) && "00902001" IN SELECTCOLUMNS(RELATEDTABLE(lancamen),"__codproprio",[codproprio]) && (NOT("00103001" IN SELECTCOLUMNS(RELATEDTABLE(lancamen),"__codproprio",[codproprio])) || NOT("00000501" IN SELECTCOLUMNS(RELATEDTABLE(lancamen),"__codproprio",[codproprio]))) ) RETURN IF(COUNTROWS(__Table) > 0,1,BLANK())
8 Replies
This is very difficult to unwind. Better to post sample data, expected output and a natural language description of what you are trying to achieve. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Ok, thanks for the tip!
Command executed in database:
select b.numconta,b.codplaco
from contas b
WHERE b.codplaco ='IPN001'
AND b.databert >= '2019-12-15'
AND exists(SELECT 1 from lancamen a WHERE a.numconta=b.numconta and a.codproprio IN ('00902001'))
AND (not exists(SELECT 1 from lancamen a WHERE a.numconta=b.numconta and a.codproprio IN ('00103001'))
OR not exists(SELECT 1 from lancamen a WHERE a.numconta=b.numconta and a.codproprio IN ('00000501')))Result:
numconta codplaco 230374 IPN001 235173 IPN001 239382 IPN001 242627 IPN001 Sample data:
https://1drv.ms/x/s!AslxtkoxDzeW9h-wpNlN-bbp6rxf?e=E9CKkM
someone help me...😨
