Replicating the SQL Query in to DAX

[sjar1290]

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

[foodd]

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

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.

[sjar1290]

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