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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Yannick63
Frequent Visitor

SQL to DAX Conversion Need Help

Hello Expert,

I'm looking for anyone which can help me to translate this SQL query in DAX

 

select
'EUR' zone,
ALTERNATE_BOM_DESIGNATOR,
hr_organization_units.name operating_unit,
hr_all_organization_units.attribute5 site,
org_organization_definitions.organization_code,
hr_all_organization_units.name organization_name,
fnd_lookup_values.meaning COUNTRY,
bom.bill_sequence_id,
level niveau,
(select msi.segment1 from mtl_system_items msi where msi.inventory_item_id=bom.assembly_item_id and msi.organization_id=85) parent_item,
lpad(' ',2*(level-1),' ')||
(select msi.segment1 from mtl_system_items msi where
msi.inventory_item_id=bom_inventory_components.component_item_id and msi.organization_id=575) child_item,
bom_inventory_components.component_item_id child_item_id,
bom_inventory_components.bill_sequence_id ,
bom_inventory_components.operation_seq_num ,
bom_inventory_components.bom_item_type ,
bom_inventory_components.item_num item_position,
bom_inventory_components.component_quantity,
bom_inventory_components.component_yield_factor
from
bom_inventory_components
join
(select * from bom_bill_of_materials
where 1=1
and organization_id =575
and ALTERNATE_BOM_DESIGNATOR='2018') bom
on (bom.bill_sequence_id=bom_inventory_components.bill_sequence_id)
join hr_all_organization_units
on hr_all_organization_units.organization_id=bom.organization_id
join org_organization_definitions
on org_organization_definitions.organization_id = hr_all_organization_units.organization_id
join mich_gss_organizations
on mich_gss_organizations.organization_id = hr_all_organization_units.organization_id
join hr_organization_units
on hr_organization_units.organization_id = mich_gss_organizations.operating_unit
left join fnd_lookup_values
on (fnd_lookup_values.lookup_type = 'MICH_GSS_COUNTRIES'
and fnd_lookup_values.lookup_code = substr(org_organization_definitions.organization_name, 5, 2)
and fnd_lookup_values.language ='US')

where 1=1
start with bom.assembly_item_id=100190
connect by prior bom_inventory_components.component_item_id=bom.assembly_item_id;

 

 

 

2 REPLIES 2
Yannick63
Frequent Visitor

Hello @S_JB  and all experts

I'm not allowed by my organisation to use this kind of gateway so I need to first import everything from the datalake and make this conversion in PowerBi.

 

It is link to my other post where I explain the goal of this.

https://community.powerbi.com/t5/Desktop/How-to-explore-a-nomenclature/m-p/1508561

 

S_JB
Resolver III
Resolver III

Are you importing your data into Power BI desktop directly from the database?

 

If so, when creating the connection to your database to import the date you can go to advanced options which allows you to paste your SQL logic to create your dataset. There are certain things you would need to consider with this though. Please see the below link for additional information:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-sql-tutorial 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.