cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mohammadwazeri
Helper II
Helper II

How to Convert This Select Statement From SQL Into Power Bi

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

2 REPLIES 2
RicoZhou
Community Support
Community Support

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.

Hi @RicoZhou ,

 

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.

 

Mohammadwazeri_0-1669932373014.png

 

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

 

Thank you so much,

Mohammad

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.