Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Visualization to show Country only

Hi Everyone, I have a data set currently that has columns of information containing city and country. For example:  When i create a visualization it will show as:   Is there anyway that i ...
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    You can create columns like DAX below.

     

    Country =
var FirstComma = FIND(",",'Table1'[Location],1)
var SecondComma = FIND(",",'Table1'[Location],FirstComma + 1)
var LengthOfString = LEN('Table1'[Location])
return
MID('Table1'[Location],SecondComma + 1, LengthOfString - SecondComma + 1)
 
City =
var FirstComma = FIND(",",'Table1'[Location],1)
var SecondComma = FIND(",",'Table1'[Location],FirstComma + 1)
return
LEFT(Table1[Location],SecondComma-1)

     

     

     

     

     

    Best Regards,

    Amy

     

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous,

 

The Power Query Editor makes this possible! The first thing you're going to want to do is to open the query editor and navigate to the "add column" ribbon. You will click on extract, as shown in the visual below.

 

You will use text after delimiter.  Use the screenshot below to enter in the values.

 

Once you click ok, you should end up with two columns like the screenshot below.  Make sure to rename the column! This will allow you to group by country.

 

 

You can also do this using DAX. If you want to know how to do that, let me know!

 

Hope this helped!

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous 

 

Thank you！If you can let me know how to do this as a DAX formula will be great because i am building a dashboard on an app and will be manually updating the excel file every few weeks so i want the visualization to update automatically without much modifications as possible in BI!