SQL to DAX Conversion Need Help

[Yannick63]

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;

[Yannick63]

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]

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