DAX help

[Anonymous]

Hi all,

I have metadatas_ column which is like in below format

{"v":[{"v":{"f":[{"v":"40187"},{"v":"life"},{"v":[{"v":{"f":[{"v":"en"},{"v":"Learn"}]}}]},{"v":[]}]}},{"v":{"f":[{"v":"57236"},{"v":"live"},{"v":[{"v":{"f":[{"v":"en"},{"v":"China"}]}},{"v":{"f":[{"v":"fr"},{"v":"Chine"}]}},{"v":{"f":[{"v":"it"},{"v":"Cina"}]}},{"v":{"f":[{"v":"pl"},{"v":"Chiny"}]}},{"v":{"f":[{"v":"sr"},{"v":"Kina"}]}}]},{"v":[]}]}},{"v":{"f":[{"v":"50267"},{"v":"live"},{"v":[{"v":{"f":[{"v":"en"},{"v":"India and Asia Pacific"}]}}]},{"v":[]}]}}]}

In this it contains both China, India and Asia Pacific

So like wise if metadata_ contains more than one of following values “China, Enlarged Europe, India and Asia Pacific, Middle East & Africa, North America, South America” it should give output as for those rows  as Locality
If metadata_ contains any one of following values “China, Enlarged Europe, India and Asia Pacific, Middle East & Africa, North America, South America” it should give output for those rows  as Region

also if metadatas_ contains text like "Globe" it should give output for those rows as Globe

all this condition need to created in a single column

Please Help to achieve this!!!!

Thanks in advance

[ryan_mayu · Accepted solution]

@Anonymous 

maybe you can try this

Column = 
VAR _c=if(SEARCH("China",'Table'[metadatas],1,0)>0,1,0)
VAR _ee=if(SEARCH("Enlarged Europe",'Table'[metadatas],1,0)>0,1,0)
VAR iap=if(SEARCH("India and Asia Pacific",'Table'[metadatas],1,0)>0,1,0)
VAR mea=if(SEARCH("Middle East & Africa",'Table'[metadatas],1,0)>0,1,0)
VAR na=if(SEARCH("North America",'Table'[metadatas],1,0)>0,1,0)
VAR sa=if(SEARCH("South America",'Table'[metadatas],1,0)>0,1,0)
var g=if(SEARCH("Globe",'Table'[metadatas],1,0)>0,1,0)
return if(g=1,"Globe",if(_c+_ee+iap+mea+na+sa>1,"Locality",if(_c+_ee+iap+mea+na+sa=1,"Region")))

[Anonymous]

Hi Ryan,

Thanks a lot
It helped...

[ryan_mayu]

you are welcome

[Anonymous]

Hi,

one more help....can you please help me to get it in power query as a custom column....it will be helpful

Thanks
sai

[ryan_mayu]

@Anonymous 

I have provided a solutin in PQ, pls see the attachment below