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

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