If condition based on different tables in power bi

[AhmedIqbal]

Hello, 
I am currently working on a dashboard for Accounts Payable. This is to show the live statistics of invoices and who they are allocated to. The invoices are allocated to analysts per the company code. However, If a company code received high volume of invoices, they are reallocated per the document ID (Invoice). I would like to create a column to state that if a value is present in the reallocation column, then use the reallocation name instead of the analyst in charge of the company code. Please advise if this approach is feasible or if there are any other solutions to resolve this. Thank you!

[BA_Pete]

Hi @AhmedIqbal ,

Try the following measure:

finalAnalyst =
IF(
  ISBLANK(Reallocation[Reallocation Analyst]),
  Allocation[Analyst]
)

Pete 

[AhmedIqbal]

Hey Pete, 
I'm getting this error when I used the below formula.

[BA_Pete]

Hi @AhmedIqbal ,

Sorry, I wasn't in front of my computer when I sent that measure. Try this one:

finalAnalyst = 
IF(
  ISBLANK(MAX(Reallocation[Reallocation Analyst])),
  MAX(Allocation[Analyst]),
  MAX(Reallocation[Reallocation Analyst])
)

I get this output:

Pete

[AhmedIqbal]

Hi Pete, 
This works! But I think my relationship was incorrect. Please advise the measure i should use to get the same output if my relationship is as below. Thank you!

[BA_Pete]

Hi @AhmedIqbal ,

I don't think the relationships will prevent this measure from working within a table visual as provided, as the context is picked up from the table row.

Are you seeing unexpected results in your table after changing the relationships?

Pete

[AhmedIqbal]

Hey Pete, 
Yes I am getting an unexpected error. The lines are duplicating now. I have saved the file in the link shared previously. 

[BA_Pete · Accepted solution]

Hi @AhmedIqbal ,

This is because you have also changed your Cross Filter direction between Allocation and Supplier to 'Single' in your file, whereas it was 'Both' in the screenshot you provided.

Pete

[AhmedIqbal]

Thank you Pete!

[BA_Pete]

No worries, happy to help 🙂

[dax]

Hi  @AhmedIqbal , 

When you use measure , you need to use aggregate expression in expression , you could try "if(isblank(min([column])), [columnB])". By the way, I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AhmedIqbal]

Hi Zoe,
You can access the power bi file via this link. Power Bi File 
I am trying to create a new column that will pull the name based on the Allocation and Reallocation. If data is present in Reallocation, then use that name. If not present, then use the Allocation name. 

[lbendlin]

Have you tried to add the  Power Query code for your condition, if so, where are you stuck?

Note: 1:1 relationships are ususally a sign that the tables can be combined/merged.  Is that true in your scenario?