Forum Discussion
Filter SQL Query upon Import
Hi Anonymous
your query should be something like this:
= Sql.Database("XXXXX", [Query= "SELECT DIS.Location_cd , DPR.ProductGroup_nm , DPR.ProductGroup_cd , DPR.ProductSequence_nm , DPR.ProductSequence_cd , DDT.Fiscal_year_nr , DDT.Fiscal_month_year_nr , DDT.Fiscal_week_year_nr --METRICS , sum(FID.Units) AS Units , sum(FID.Sales) AS Sales , sum(FID.GL_Gross_Profit_FIFO) AS GL_Gross_Profit_FIFO , sum(FID.Standard_Gross_Profit) AS Standard_Gross_Profit -- select count(1) FROM DW.vwFact_InvoiceDetail(NOLOCK) FID INNER JOIN DW.vwDim_Currency DCR ON DCR.CurrencyKey = FID.CurrencyKey INNER JOIN DW.vwDim_InvoiceDetail DID ON DID.InvoiceDate_id = FID.InvoiceDate_id AND DID.InvoiceDetail_id = FID.InvoiceDetail_id INNER JOIN DW.vwDim_Date DDT ON DDT.Date_id = FID.InvoiceDate_id INNER JOIN DW.vwDim_Organization DOR ON DOR.Organization_id = FID.Organization_id INNER JOIN DW.vwDim_Product DPR ON DPR.Product_id = FID.Product_id INNER JOIN DW.vwDim_InvoiceHeader DIH ON DIH.Invoice_id = FID.Invoice_id INNER JOIN DW.vwDim_Customer_OLAP DIB ON DIB.Customer_id = FID.BillCustomer_id AND DIB.Customer_Class_cd != '28' INNER JOIN DW.vwDim_Customer_OLAP DIS ON DIS.Customer_id = FID.ShipCustomer_id WHERE DID.Revenue_GL_no IN ( '40001' ,'40002' ,'40003' ,'40004' ,'40005' ,'40006' ,'40007' ,'40008' ,'40101' ,'40102' ,'40103' ,'40104' ,'40105' ,'40107' ,'40108' ,'50001' ,'50002' ,'50003' ,'50005' ,'50101' ,'50102' ,'50103' ,'50105' ) AND DID.Record_cd IN ('B','C','D','G','N') AND DIH.Preacquisition_flg ='N' AND DOR.Reporting_Division_cd IN ('D01','D99') AND DIS.Customer_Channel_nm ='CORE' AND DDT.Fiscal_year_nr IN (2019) -- AND ddt.Fiscal_week_year_nr = 201712 AND DIS.Location_cd IN (" & TheCommaDelimitedValuesShownInMyFirstReply & ") GROUP BY DIS.Location_cd , DPR.ProductGroup_nm , DPR.ProductGroup_cd , DPR.ProductSequence_nm , DPR.ProductSequence_cd , DDT.Fiscal_year_nr , DDT.Fiscal_month_year_nr , DDT.Fiscal_week_year_nr"])
I started looking and found the translation from SQL into DAX. When you go to the Query > click into Source on steps and shows up in the top row.
In my import, "Location_cd" is "Customer #" I want to filter on.
See below:
= Sql.Database("12345, "XXYYZZ", [Query="SELECT DIS.Location_cd#(lf), DPR.ProductGroup_nm#(lf), DPR.ProductGroup_cd#(lf), DPR.ProductSequence_nm#(lf), DPR.ProductSequence_cd#(lf), DDT.Fiscal_year_nr#(lf), DDT.Fiscal_month_year_nr#(lf), DDT.Fiscal_week_year_nr#(lf)--METRICS#(lf), sum(FID.Units) AS Units#(lf), sum(FID.Sales) AS Sales#(lf), sum(FID.GL_Gross_Profit_FIFO) AS GL_Gross_Profit_FIFO#(lf), sum(FID.Standard_Gross_Profit) AS Standard_Gross_Profit -- select count(1) #(lf)FROM DW.vwFact_InvoiceDetail(NOLOCK) FID #(lf)INNER JOIN DW.vwDim_Currency DCR ON DCR.CurrencyKey = FID.CurrencyKey#(lf)INNER JOIN DW.vwDim_InvoiceDetail DID ON DID.InvoiceDate_id = FID.InvoiceDate_id AND DID.InvoiceDetail_id = FID.InvoiceDetail_id#(lf)INNER JOIN DW.vwDim_Date DDT ON DDT.Date_id = FID.InvoiceDate_id#(lf)INNER JOIN DW.vwDim_Organization DOR ON DOR.Organization_id = FID.Organization_id#(lf)INNER JOIN DW.vwDim_Product DPR ON DPR.Product_id = FID.Product_id#(lf)INNER JOIN DW.vwDim_InvoiceHeader DIH ON DIH.Invoice_id = FID.Invoice_id#(lf)INNER JOIN DW.vwDim_Customer_OLAP DIB ON DIB.Customer_id = FID.BillCustomer_id AND DIB.Customer_Class_cd != '28'#(lf)INNER JOIN DW.vwDim_Customer_OLAP DIS ON DIS.Customer_id = FID.ShipCustomer_id#(lf)WHERE DID.Revenue_GL_no IN (#(lf) '40001' ,'40002' ,'40003' ,'40004' ,'40005' ,'40006'#(lf) ,'40007' ,'40008' ,'40101' ,'40102' ,'40103' ,'40104'#(lf) ,'40105' ,'40107' ,'40108' ,'50001' ,'50002' ,'50003'#(lf) ,'50005' ,'50101' ,'50102' ,'50103' ,'50105'#(lf) )#(lf)AND DID.Record_cd IN ('B','C','D','G','N') #(lf)AND DIH.Preacquisition_flg ='N'#(lf)AND DOR.Reporting_Division_cd IN ('D01','D99')#(lf)AND DIS.Customer_Channel_nm ='CORE'#(lf)AND DDT.Fiscal_year_nr IN (2019)#(lf)-- AND ddt.Fiscal_week_year_nr = 201712#(lf)GROUP BY DIS.Location_cd#(lf), DPR.ProductGroup_nm#(lf), DPR.ProductGroup_cd#(lf), DPR.ProductSequence_nm#(lf), DPR.ProductSequence_cd#(lf), DDT.Fiscal_year_nr#(lf), DDT.Fiscal_month_year_nr#(lf), DDT.Fiscal_week_year_nr", CreateNavigationProperties=false])
Hi Anonymous
the query should be something like this:
= Sql.Database("XXXXX", [Query= "SELECT
DIS.Location_cd ,
DPR.ProductGroup_nm ,
DPR.ProductGroup_cd ,
DPR.ProductSequence_nm ,
DPR.ProductSequence_cd ,
DDT.Fiscal_year_nr ,
DDT.Fiscal_month_year_nr ,
DDT.Fiscal_week_year_nr --METRICS ,
sum(FID.Units) AS Units ,
sum(FID.Sales) AS Sales ,
sum(FID.GL_Gross_Profit_FIFO) AS GL_Gross_Profit_FIFO ,
sum(FID.Standard_Gross_Profit) AS Standard_Gross_Profit -- select count(1)
FROM DW.vwFact_InvoiceDetail(NOLOCK) FID INNER JOIN DW.vwDim_Currency DCR ON DCR.CurrencyKey = FID.CurrencyKey
INNER JOIN DW.vwDim_InvoiceDetail DID ON DID.InvoiceDate_id = FID.InvoiceDate_id AND DID.InvoiceDetail_id = FID.InvoiceDetail_id
INNER JOIN DW.vwDim_Date DDT ON DDT.Date_id = FID.InvoiceDate_id
INNER JOIN DW.vwDim_Organization DOR ON DOR.Organization_id = FID.Organization_id
INNER JOIN DW.vwDim_Product DPR ON DPR.Product_id = FID.Product_id
INNER JOIN DW.vwDim_InvoiceHeader DIH ON DIH.Invoice_id = FID.Invoice_id
INNER JOIN DW.vwDim_Customer_OLAP DIB ON DIB.Customer_id = FID.BillCustomer_id AND DIB.Customer_Class_cd != '28'
INNER JOIN DW.vwDim_Customer_OLAP DIS ON DIS.Customer_id = FID.ShipCustomer_id
WHERE DID.Revenue_GL_no IN ( '40001' ,'40002' ,'40003' ,'40004' ,'40005' ,'40006' ,'40007' ,'40008' ,'40101' ,'40102' ,'40103' ,'40104' ,'40105' ,'40107' ,'40108' ,'50001' ,'50002' ,'50003' ,'50005' ,'50101' ,'50102' ,'50103' ,'50105' )
AND DID.Record_cd IN ('B','C','D','G','N')
AND DIH.Preacquisition_flg ='N'
AND DOR.Reporting_Division_cd IN ('D01','D99')
AND DIS.Customer_Channel_nm ='CORE'
AND DDT.Fiscal_year_nr IN (2019) --
AND ddt.Fiscal_week_year_nr = 201712
AND DIS.Location_cd IN (" & TheCommaDelimitedValuesShownInMyFirstReply & ")
GROUP BY DIS.Location_cd , DPR.ProductGroup_nm , DPR.ProductGroup_cd , DPR.ProductSequence_nm ,
DPR.ProductSequence_cd , DDT.Fiscal_year_nr , DDT.Fiscal_month_year_nr , DDT.Fiscal_week_year_nr"])