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

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    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

      That's right, thank you Greg_Deckler 

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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

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