Assign first value available from an other table

[Sima007]

Hello everybody

I have 2 tables :

1. Products table with product code, equipment type, index wich is a priority, and count of product : if a product need 2 places so i have the same product twice. (it's a dax calculated table)
2. List of storage address by equipment type and also indexed by priority.

For each row in Product Table, i need to assign first address found and flag the address to avoid assigning to another row. and if a product has multiple row, so the first occurence will take the first non assigned address, and the other occurence will take the first non assigned address in the same aisle and side.

Hope it's clear

Thanks 

[v-huijiey-msft]

Hi @Sima007 ,

I tried it and found it difficult to achieve this requirement.

Your requirement needs to be checked line by line to determine whether the conditions are met - that is, addresses cannot be repeated.

My suggestion is that if possible, consider assigning addresses to products based on your criteria in a database or using a scripting language like Python.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Sima007]

Hello @v-huijiey-msft

is it possible to use Python in power bi to do this calculation?

Thanks

[v-huijiey-msft · Accepted solution]

Hi @Sima007 ,

Yes, you can.

Before you begin, you need to install Python and Pandas.

In Power BI desktop, specify the installation path of Python.

File > Options and settings > Options > Python scripting

In the Power Query editor, select and enter the Python code for the calculation.

Transform > Run Python script

There are some limitations for queries contained in Python scripts created in Power Query:

All Python data source settings must be set to Public, and all other steps in queries created in the Power Query Editor must be set to Public.

To enable scheduled refresh for a Python visual or dataset, you need to enable Scheduled Refresh and install Personal Gateway on the computer where the workbook and Python installation are stored.

Nested tables (tables within tables) are not currently supported.

For more information see:

Use Python in Power BI Power Query Editor - Power BI | Microsoft Learn

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Sima007]

Hello, Can any one help please ? if i had to do it in Excel i would do a loop like this. but i don't really know how to do it with Dax

N = product_table_row_count
k = Address_table_row_count
Addcolumns(Address_table,[FLAg]) 
Address_table[Flag] = 0 //initialized by 0
for i = 1 to n
if Product_table[Occurence] = 1 // first occurence of product
for j = 1 to k
if AddressTable(j,"Flag")=0 then
Product_table(i,Address) = Address_table(j,Address])
AddressTable(j,"Flag")=1
end if
next j
else
for j = 1 to k
>> find the address beside the first occurence off product with flag = 0
AddressTable(j,"Flag")=1
next j
next i

 thank you