Forum Discussion

MaiconM1's avatar
MaiconM1
Frequent Visitor
6 years ago
Solved

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(...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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's avatar
    Greg_Deckler
    6 years ago

    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's avatar
MaiconM1
Frequent Visitor
6 years ago

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:

 

numcontacodplaco
230374IPN001
235173IPN001
239382IPN001
242627IPN001

 

Sample data:  

https://1drv.ms/x/s!AslxtkoxDzeW9h-wpNlN-bbp6rxf?e=E9CKkM

 

  • MaiconM1's avatar
    MaiconM1
    Frequent Visitor
    6 years ago

    someone help me...😨

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      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's avatar
        MaiconM1
        Frequent Visitor
        6 years ago
         

        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