SQL to DAX

[MaiconM1]

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

[Greg_Deckler · Accepted solution]

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

[Greg_Deckler]

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

[MaiconM1]

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

[MaiconM1]

someone help me...😨

[Greg_Deckler · Accepted solution]

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

[MaiconM1]

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

[Greg_Deckler · Accepted solution]

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

[MaiconM1]

That's right, thank you @Greg_Deckler 

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

[Greg_Deckler]

@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!!