Forum Discussion

ruut's avatar
ruut
Frequent Visitor
8 years ago
Solved

translate simple access sql query to DAX

I have written the following Access SQL Query:

    SELECT Field1, Field2 * IIf(Field3>Field4,2,3) AS Field5
    FROM Table1
    WHERE Field6=1
    
    UNION ALL
    
    SELECT Field7, Field8
    FROM Table2

which I would like to rewrite in Power BI DAX language. How to get started?

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi ruut,

     

    Create a calculated table by clicking the "New table" button under "Modeling" tab, please refer to below DAX:

    Union Table =
UNION (
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Field6] = 1 ),
        "Field1", Table1[Field1],
        "Field5", Table1[Field2]
            * ( IF ( Table1[Field3] > Table1[Field4], 2, 3 ) )
    ),
    SELECTCOLUMNS ( Table2, "Field7", Table2[Field7], "Field8", Table2[Field8] )
)

    Best regards,
    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi ruut,

     

    Create a calculated table by clicking the "New table" button under "Modeling" tab, please refer to below DAX:

    Union Table =
UNION (
    SELECTCOLUMNS (
        FILTER ( Table1, Table1[Field6] = 1 ),
        "Field1", Table1[Field1],
        "Field5", Table1[Field2]
            * ( IF ( Table1[Field3] > Table1[Field4], 2, 3 ) )
    ),
    SELECTCOLUMNS ( Table2, "Field7", Table2[Field7], "Field8", Table2[Field8] )
)

    Best regards,
    Yuliana Gu