Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
v-rzhou-msft
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 @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.

 

Mohammadwazeri_0-1669932373014.png

 

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

 

Thank you so much,

Mohammad

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors