Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Replicating the SQL Query in to DAX

Hi All,

 

I need help replicate the SQL Query in to DAX as a calculated column or calculated table, due to the volume of the data I can't use the SQL Query directly in power BI.

below is the SQL Query

 

SELECT ,IOH.VALUATED_UNRSTR_STK_AMT *cc.CRNCY_CONV_RATE_AMT VALUATED_UNRSTR_STK_AMT_CONV , cc.CRNCY_CONV_TYP_LKEY, cc.TO_CRNCY_KEY,cc.CRNCY_CONV_RATE_AMT,cc.FROM_CRNCY_KEY,
FC.DY_DTE, FC.FMTH_END_DTE, FC.FQTR_END_DTE,
FC.WK_END_DTE, FC.FYR_END_DTE
FROM TEST_MARTS.INVENTORY.VW_BRZ_INVENTORY_ONHAND IOH
INNER JOIN ( SELECT DY_DTE, FQTR_END_DTE, WK_END_DTE, FYR_END_DTE,
MAX(FMTH_END_DTE) AS FMTH_END_DTE FROM PROD_EDW.DIMENSIONS.DIM_FISCAL_CALENDAR
WHERE DY_DTE <= CURRENT_DATE AND
DY_DTE > DATEADD(MONTH, -36, CURRENT_DATE)
GROUP BY DY_DTE, FMTH_END_DTE, FQTR_END_DTE, WK_END_DTE, FYR_END_DTE) FC
ON Date(IOH.EXPR_DTE) > FC.DY_DTE AND Date(IOH.EFF_DTE) <= FC.DY_DTE
left join
PROD_EDW.EDW_REFDATA.CURRENCYXREF xref on xref.CRNCY_KEY = IOH.CRNCY_KEY and IOH.SRC_SYS_KEY =xref.SRC_SYS_KEY
left join
(select * from TEST_EDW.DIMENSIONS.DIM_CURRENCY_CONVERSION
where SRC_SYS_KEY like 'HFM_CLC' ) cc
on xref.SCRNCY_CD=cc.FROM_CRNCY_KEY and FC.FMTH_END_DTE between cc.CONV_RATE_EFF_DTE and
cc.CONV_RATE_EXPIRY_DTE
where IOH.VALUATED_UNRSTR_STK_QTY <> 0 and IOH.VALUATED_UNRSTR_STK_AMT<>0

 

 

your help would be much appreciated.

 

Thank you,

Raj

Interesting Links
Need Help

2 Replies

  • foodd's avatar
    foodd
    Community Champion
    3 years ago

    Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

    https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

    This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,
      I am giving link to the PBIX file  with tables with some sample data, can't create joins between VW table and Calendar table , that need to be take care with in dax.
      https://we.tl/t-WGXVtSwUNe
      this is the SQL Query I am trying to replicate in Power BI using DAX
      SELECT IOH.VALUATED_UNRSTR_STK_AMT *cc.CRNCY_CONV_RATE_AMT VALUATED_UNRSTR_STK_AMT_CONV , cc.CRNCY_CONV_TYP_LKEY, cc.TO_CRNCY_KEY,cc.CRNCY_CONV_RATE_AMT,cc.FROM_CRNCY_KEY,
      FC.DY_DTE, FC.FMTH_END_DTE, FC.FQTR_END_DTE,
      FC.WK_END_DTE, FC.FYR_END_DTE
      FROM TEST_MARTS.INVENTORY.VW_BRZ_INVENTORY_ONHAND IOH
      INNER JOIN ( SELECT DY_DTE, FQTR_END_DTE, WK_END_DTE, FYR_END_DTE,
      MAX(FMTH_END_DTE) AS FMTH_END_DTE FROM PROD_EDW.DIMENSIONS.DIM_FISCAL_CALENDAR
      WHERE DY_DTE <= CURRENT_DATE AND
      DY_DTE > DATEADD(MONTH, -36, CURRENT_DATE)
      GROUP BY DY_DTE, FMTH_END_DTE, FQTR_END_DTE, WK_END_DTE, FYR_END_DTE) FC
      ON Date(IOH.EXPR_DTE) > FC.DY_DTE AND Date(IOH.EFF_DTE) <= FC.DY_DTE
      left join
      PROD_EDW.EDW_REFDATA.CURRENCYXREF xref on xref.CRNCY_KEY = IOH.CRNCY_KEY and IOH.SRC_SYS_KEY =xref.SRC_SYS_KEY
      left join
      (select * from TEST_EDW.DIMENSIONS.DIM_CURRENCY_CONVERSION
      where SRC_SYS_KEY like 'HFM_CLC' ) cc
      on xref.SCRNCY_CD=cc.FROM_CRNCY_KEY and FC.FMTH_END_DTE between cc.CONV_RATE_EFF_DTE and
      cc.CONV_RATE_EXPIRY_DTE
      where IOH.VALUATED_UNRSTR_STK_QTY <> 0 and IOH.VALUATED_UNRSTR_STK_AMT<>0

      your help would be much appreciated.

      Thank you,

      Raj