topic Re: SQL to DAX in DAX Commands and Tips

SQL to DAX

MaiconM1 — Wed, 04 Mar 2020 13:12:59 GMT

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')))

Re: SQL to DAX

Greg_Deckler — Wed, 04 Mar 2020 14:15:39 GMT

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

Re: SQL to DAX

MaiconM1 — Wed, 04 Mar 2020 19:20:40 GMT

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

Re: SQL to DAX

MaiconM1 — Fri, 06 Mar 2020 14:02:54 GMT

someone help me...😨

Re: SQL to DAX

Greg_Deckler — Fri, 06 Mar 2020 14:21:30 GMT

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())

Re: SQL to DAX

MaiconM1 — Fri, 06 Mar 2020 15:02:30 GMT

Hello Greg_Deckler.
The command will serve to find "numconta" that have "lancamen" with "codproprio" = 00902001, but do not have "lancamen" = 00103001 and 00000501

Today I use Grafana to do this monitoring, but I want to use Power BI

Re: SQL to DAX

Greg_Deckler — Fri, 06 Mar 2020 16:01:15 GMT

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())

Re: SQL to DAX

MaiconM1 — Fri, 06 Mar 2020 16:21:39 GMT

That's right, thank you Greg_Deckler

I will do some tests, any questions I post a new question.

Re: SQL to DAX

Greg_Deckler — Fri, 06 Mar 2020 16:23:20 GMT

MaiconM1 That may not be the best/most efficient manner of doing that, I was hacking it together trying to interpret your SQL code as I went so just let me know!!