If Statement for a simple condition

[harrinho]

Hi, 

I have a major headache with a statement I want to create and it is dead simple. However, PowerBI doesn't allo me to create it.

I have two tables which are joined together with a relationship on the product name and I can see for every project ID for example, the values of another measure from the other table. In the screenshot below Project ID, Product and Product Split % comes from table A. BU and Product come from table B. 

What I want is to create a new Top BU let's say field, which will pick up the BU with the highest product split. For example, Project S009301 spans across thre different BUs (AO, AUTO and ITSM). However, I want my Top BU field to show only ITSM as this BU accounts for the 70% of the project. In the occasion of an equal split i.e. 50/50 or 30/35/35 I'd like to pick the one which has the highest amount but this is secondary. The major concern is to pick up the BU with the highest product split %. 

In doing that, I tried to create a column in Table B i.e. IF(Table A[product split %)<>1 etc. but it doesn't even allow me to use a field from Table A in my condition. 

Is there any way to achieve this? 

Thanks

[Anonymous]

@harrinho,

Please check if the DAX in the PBIX file  below returns your expected result. If not, please share more details about your tables.

https://1drv.ms/u/s!AhsotbnGu1Nok1U_WU06vmFffBca

Regards,
Lydia

[Phil_Seamark]

HI @harrinho

Which table is on the 1 side (Table A or Table B), and which table is on the many side?

[harrinho]

Thanks for getting back @Phil_Seamark. Table A is the many (Project ID, Product, Product Split %) and BU and Product is Table B. The two tables are joined on the Product name 

[Anonymous]

@harrinho,

Does my DAX return your expected result?

Regards,
Lydia