How to Convert This Select Statement From SQL Into Power Bi

[Mohammadwazeri]

Hi Guys,

I am having a hard to convert the below query into Power BI especially the last part "HAVING Count(T0.WhsCode)=1)." 

Any idea would be helpful.

                   (SELECT T0.ItemCode, '05-MRB' AS Whse FROM OITW T0

                    INNER JOIN (SELECT DISTINCT ItemCode FROM OITW WHERE WhsCode = '05-MRB') T1 ON                                                                T0.ItemCode=T1.ItemCode

                    GROUP BY T0.ItemCode

                    HAVING Count(T0.WhsCode)=1) T4 ON T0.Father=T4.ItemCode

I appreciate your help in advance!

Thank you,

Mohammad

[v-rzhou-msft]

Hi @Mohammadwazeri ,

As far as I know, it will be easier for us to create the dax code directly instead of convert from SQL script.

So I suggest you to share a sample file with us and show us a screenshot with the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mohammadwazeri]

Hi @v-rzhou-msft ,

Thanks for replying!

The query I shared with you guys is part of a big query. I am sharing only the part of the query that joining tables start, there are more fields include in this query I thought which won't make sense to share except taking spaces. The query is in BOLD line

T2.PrdStdCst AS 'Component Prod Std Cost',

(T0.Quantity * T2.PrdStdCst) AS 'Extended Prod Std Cost',

(SELECT T5.price FROM ITM1 T5 WHERE T0.Code = T5.ItemCode AND T5.PriceList = 3) AS 'Cost Estimate',

(SELECT T5.price * T0.quantity FROM ITM1 T5 WHERE T0.Code = T5.ItemCode AND T5.PriceList = 3) AS 'Extended Cost Estimate',

T2.LastPurPrc AS 'Last Purchase Price',

(T2.LastPurPrc * T0.Quantity) AS 'Extended Last Purchase Price'

FROM  [dbo].[ITT1] T0

INNER JOIN OITM T1 ON T0.Father = T1.ItemCode

LEFT OUTER JOIN OITM T2 ON T0.Code = T2.ItemCode

LEFT OUTER JOIN ORSC T3 ON T0.Code = T3.VisResCode

LEFT OUTER JOIN

                    ( 

                     SELECT T0.ItemCode, '05-MRB' AS Whse

                     FROM OITW T0

                     INNER JOIN (SELECT DISTINCT ItemCode FROM OITW WHERE WhsCode = '05-MRB') T1 ON                           T0.ItemCode=T1.ItemCode

                     GROUP BY T0.ItemCode

                     HAVING Count(T0.WhsCode)=1

                     )   T4     ON T0.Father=T4.ItemCode

WHERE T4.Whse IS NULL

ORDER BY T0.Father, T0.visorder

Below is a screenshot from the data, so basically all the fields pulled from other tables in the query and the part of the join which is in parentheses (Italic Font) only filters to show specific data.

I hope this is what you need to find the solution.

Thank you so much,

Mohammad