need help to create a category field

[Anonymous]

Hi,

I needed to categorise passengers based on his flight origin country and flight destination country as Domestic, International Arrival and International Departure. 

The below is the sample dataset :

origin  country	destination country	Category
India	India	Domestic
US	India	International Arrival
India	Germany	International Departure

please help me how to achieve this.

thanks

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(Data[Destination Country]="India",if(Data[Origin Country]="India","Domestic","International Arrival"),"International Departure")

The assumption in this formula is that India will always be in a row (either in the Origin Country column or in the Destination Country column i.e. there will never be a case like Sweden and Germany in the same row)

Hope this helsp.

[mahoneypat]

Please try this expression in your column 

Category = SWITCH(TRUE(), Table[origin country] = Table[destination country], "Domestic",

Table[origin country] = "US", "International Arrival",

"International Departure")

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hi @mahoneypat ,

this solution works correct logically but it shows one more category as blanks when use this as a slicer. why this shows blank and how can we remove this?

Thanks

[mahoneypat]

Not sure what you are seeing.  The way it is written, every row should at least have "International Departure", as it is the default output of SWITCH.   In any case, you could use this instead in case you have any scenarios that aren't covered by the current logic.  Does this table have a relationship with another table?  Does that table have rows in the relationship column that don't exist in this table?

Category = SWITCH(TRUE(), Table[origin country] = Table[destination country], "Domestic",

Table[origin country] = "US", "International Arrival", Table[origin country] <> "US",

"International Departure", "Other")

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[vanessafvg]

you can use the switch statement or an if statement, what are you struggling with exactly?

https://docs.microsoft.com/en-us/dax/switch-function-dax

https://www.youtube.com/watch?v=-ykkaAtlCMc