New Column in Report not getting required results

[Anonymous]

My data is a list of incoming deliveries from different cities.[Incoming]

My reference table has a list of Bays allocated to different cities.[Bay Allocation]

Many to one relationship.

My report has a list of the cities and the relevant Bay.

In report view I want add an extra column that puts "Not Allocated" where there is a blank bay.

Column = IF('Bay Allocation'[Bay]="","No Allocation",'Bay Allocation'[Bay])

However it is only returning a result where the Bay number already exists.

Is there a way of doing this or do I need to merge queries and do it that way?

Thanks for your patience and time in advance.

[Anonymous · Accepted solution]

Thank you all for your help. I managed to figure it out.
I first needed to create a RELATED column in the DATA VIEW of the Incoming Table.

Allocated Bay 1 = RELATED('Bay Allocation'[Bay])
Then create my formula based on this:

Allocated Bay = if(Incoming[Allocated Bay 1]=BLANK(),"Not Allocated",Incoming[Allocated Bay 1])

Anybody got a good link to learn the top 20 DAX formulas?

[Whitewater100]

Hi:

Are you showing a table you created on the canvas or the data table you want to add column too? If this is a data table

Result = IF('Bay Allocation'[Bay] = BLANK(), "No Allocation", 'Bay Allocation'[Bay])

I hope this works..

[Whitewater100]

Hello:

That is great. Please consider giving kudos to my reply. Thanks.

[amitchandak]

@Anonymous , Try like

Column = IF('Bay Allocation'[Bay]="" || isblank('Bay Allocation'[Bay]) ,"No Allocation",'Bay Allocation'[Bay])

[Anonymous]

Hi amitchandsk
Thanks for the response. I get the same results! It's as though the formula is only looking at the Bay Allocation data as opposed to the entire table of data.

Index, City and Date are from the "Incoming" table and I've linked it to the "Bay Allocation" table (many to 1)

I can go and create a merge query but thought that this would be a simple excercise!

I'm clearly missing a fundamental part of how this works !