Forum Discussion

r1cokoro's avatar
r1cokoro
Regular Visitor
4 years ago
Solved

SQL Statement to Dax

Hi All, I need help converting a SQL query to DAX cause at this point my DAX code looks weird and it doesn't work,   There are data in USD and DOP currencys, so I would like to have a result like ...
Need Help
  • r1cokoro's avatar
    r1cokoro
    4 years ago

    Well doing some research on Microsoft DOCS, I found the way I can translate the query,

    Monto = 
VAR CURRENCY = SELECTEDVALUE(DIM_MONEDA[key])
RETURN (
    SWITCH(
        TRUE(),
        CURRENCY = 1, SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="DOP"), FACT_DEALS[MONTO_COTIZADO])
            + (SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="USD"), FACT_DEALS[MONTO_COTIZADO]) * 57),
        CURRENCY = 2, SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="USD"), FACT_DEALS[MONTO_COTIZADO])
            + (SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="DOP"), FACT_DEALS[MONTO_COTIZADO]) / 57),
        CURRENCY = 3, SUM(FACT_DEALS[MONTO_COTIZADO]) + SUM(FACT_DEALS[ADJUDICADO])
    )
)

    Basically with SWITCH and SUMX I found the way to calculate the amount...

r1cokoro's avatar
r1cokoro
Regular Visitor
4 years ago

Well doing some research on Microsoft DOCS, I found the way I can translate the query,

Monto = 
VAR CURRENCY = SELECTEDVALUE(DIM_MONEDA[key])
RETURN (
    SWITCH(
        TRUE(),
        CURRENCY = 1, SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="DOP"), FACT_DEALS[MONTO_COTIZADO])
            + (SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="USD"), FACT_DEALS[MONTO_COTIZADO]) * 57),
        CURRENCY = 2, SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="USD"), FACT_DEALS[MONTO_COTIZADO])
            + (SUMX(FILTER(FACT_DEALS, FACT_DEALS[MONEDA]="DOP"), FACT_DEALS[MONTO_COTIZADO]) / 57),
        CURRENCY = 3, SUM(FACT_DEALS[MONTO_COTIZADO]) + SUM(FACT_DEALS[ADJUDICADO])
    )
)

Basically with SWITCH and SUMX I found the way to calculate the amount...