Filter out the correct customer city from column

[LasseA]

Dear all, 

I hope you can help me understand how i filter the relevant customer city from a "city column" which includes the many more cities besides the customercity. This could be the destination city, flight departure city etc. 

In excel i use to use this formula:

=IF.ERROR(VLOOKUP(A2;'HELPINGCOLUMN'!$B:$C;2;FALSE);"NOT FOUND")

Here i use a helping column i have created that includes the region "europe" and the difference cities that are. If no cities are found my formula returns "not found"

My datatable in power bi has 100's of columns. I therefor hope i can do this as a measure. 

I want to filter out the customer city for each order number.

My table has many different cities and prices and productcodes, these are all connected to order numbers, which is why there are more order numbers that are the same. 

The columns i believe are relevant to filter out the customer city are:

"OrderNumber"

"City"

"Region"

The customer city i am looking for is under the region "Europe".

How do i do this? Which measure am i to use to filter out the customer city?

If there is more than 1 customer city in the "Europe" region, then how do i choose only one?

Thanks in advance. 

[v-danhe-msft]

Hi @LasseA,

Could you please offer me some sample data to have a test and post your desired result if possible?

Regards,

Daniel He

[LasseA]

@v-danhe-msft

Order Number	Product	City	Country	Region	Desired output
51100	Flight	Johannesburg	South Africa	Africa
51100	Flight	London	United Kingdom	Europe	London
51100	Tax	Johannesburg	South Africa	Africa
51100	Hotel	Johannesburg	South Africa	Africa
51101	Flight	Johannesburg	South Africa	Africa
51101	Flight	Paris	France	Europe	Paris
51101	Flight	Nice	France	Europe
51101	Tax	Johannesburg	South Africa	Africa
51101	Hotel	Johannesburg	South Africa	Africa
51102	Flight	Johannesburg	South Africa	Africa
51102	Flight	Bremen	Germany	Europe	Bremen
51102	Tax	Johannesburg	South Africa	Africa
51102	Hotel	Johannesburg	South Africa	Africa

i cannot offer the real data, so i have tried to make a small sample of my big dataset. 

There are MANY more columns and lines for each order number which also contains different cities and regions etc. 

I hope this can give you a overview of what i am trying to write in dax. 

I prefer it as a measure and not a calculated column which i have here in the sample. 

Thank you so much 🙂

[v-danhe-msft]

Hi @LasseA,

Based on my test, you could refer to below formula:

Measure = IF(CALCULATE(MAX([Region ]))="Europe",CALCULATE(MAX([City ])),BLANK())

Result:

And I could not understand that the "Nice" city is under the region of "Europe", should it be your desired result?

You could also download the pbix file to have a view.

Regards,

Daniel He 

[LasseA]

Hello @v-danhe-msft

Have you gotten around to look at this one mor time?

I hope you will be able to help me with the additional information i provided. 

Thank you again, 

Best regards, 

Lasse

[LasseA]

Hello @v-danhe-msft

Thank you so much. 

Regarding order number 51101 with 2 cities under the region "europe" my point was that only the city Paris should be output, because i need only 1 city per order number.

I need in my visualization to show the order number only once, with 1 city. 

I hope you understand what i mean and can help me accomplish this task. 

Thank you so much in advance for your help

Best regards, 

Lasse

[LasseA]

@v-danhe-msft

Furthermore, the dax code you provided, unfortunately does not work. 

When only picking the order numer and the new measure it reveals that the syntax is wrong because order number 51102 reveals "Johannesburg" which is clearly not under the region europe. 

I hope you can help me solve this matter.

Thanks in advance and best regards 

Lasse

[LasseA]

Dear all, 

I hope you can help me understand how i filter the relevant customer city from a "city column" which includes the many more cities besides the customercity. This could be the destination city, flight departure city etc. 

In excel i use to use this formula:

=IF.ERROR(VLOOKUP(A2;'HELPINGCOLUMN'!$B:$C;2;FALSE);"NOT FOUND")

Here i use a helping column i have created that includes the region "europe" and the difference cities that are. If no cities are found my formula returns "not found"

My datatable in power bi has 100's of columns. I therefor hope i can do this as a measure. 

I want to filter out the customer city for each order number.

My table has many different cities and prices and productcodes, these are all connected to order numbers, which is why there are more order numbers that are the same. 

The columns i believe are relevant to filter out the customer city are:

"OrderNumber"

"City"

"Region"

The customer city i am looking for is under the region "Europe".

How do i do this? Which measure am i to use to filter out the customer city?

If there is more than 1 customer city in the "Europe" region, then how do i choose only one?

Order Number	Product	City	Country	Region	Desired output
51100	Flight	Johannesburg	South Africa	Africa
51100	Flight	London	United Kingdom	Europe	London
51100	Tax	Johannesburg	South Africa	Africa
51100	Hotel	Johannesburg	South Africa	Africa
51101	Flight	Johannesburg	South Africa	Africa
51101	Flight	Paris	France	Europe	Paris
51101	Flight	Nice	France	Europe
51101	Tax	Johannesburg	South Africa	Africa
51101	Hotel	Johannesburg	South Africa	Africa
51102	Flight	Johannesburg	South Africa	Africa
51102	Flight	Bremen	Germany	Europe	Bremen
51102	Tax	Johannesburg	South Africa	Africa
51102	Hotel	Johannesburg	South Africa	Africa

i cannot offer the real data, so i have tried to make a small sample of my big dataset. 

There are MANY more columns and lines for each order number which also contains different cities and regions etc. 

I hope this can give you a overview of what i am trying to write in dax. 

I prefer it as a measure and not a calculated column which i have here in the sample. 

Thank you so much 

Thanks in advance.