Combine two columns in visual

[Anonymous]

Hello Everyone,

I want to ask if it is possible to combine two columns in a column chart without changing the underlying data.

For example as shown in the picture. I have two columns that are seperate because of the way the category is saved in the source table. Because they are basically the same I want to combine them, so I have one column with 7 entries and only one of the category titles. It needs to be done in the visual because I cant change the source data.

Thanks for any help on this. maybe its not possible also.

@Greg_Deckler 

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Result:

Try to create the following measures:

cat = 
var _currentCat=MAX('Table'[category]) 
var _lenth=LEN(_currentCat)
var _delimiter=SEARCH(",",_currentCat,1,-1)
var _cat1=left(_currentCat,_delimiter-1)
var _cat2=RIGHT(_currentCat,_lenth-_delimiter)
var _cat=CONCATENATE(MIN(_cat1,_cat2),MAX(_cat1,_cat2))

return _cat

_Sum = 
var _currentCat=MAX('Table'[category]) 
var _lenth=LEN(_currentCat)
var _delimiter=SEARCH(",",_currentCat,1,-1)
var _cat1=left(_currentCat,_delimiter-1)
var _cat2=RIGHT(_currentCat,_lenth-_delimiter)
var _cat=CONCATENATE(MIN(_cat1,_cat2),MAX(_cat1,_cat2))

var _t=ADDCOLUMNS(ALL('Table'),"_cat",[cat])
var _sum=SUMX(FILTER(_t,[_cat]=_cat),[Value])

return _sum

_show = 
var _currentCat=MAX('Table'[category]) 
var _lenth=LEN(_currentCat)
var _delimiter=SEARCH(",",_currentCat,1,-1)
var _cat1=left(_currentCat,_delimiter-1)
var _cat2=RIGHT(_currentCat,_lenth-_delimiter)
var _cat=CONCATENATE(MIN(_cat1,_cat2),MAX(_cat1,_cat2))

var _t=ADDCOLUMNS(ALL('Table'),"_cat",[cat])
var _sum=SUMX(FILTER(_t,[_cat]=_cat),[Value])

var _OnlyShowOne=MINX(FILTER(_t,[_cat]=_cat),[category])
var _show=IF(_currentCat=_OnlyShowOne,1,0)
return _show

Then show items when _show is 1 in filter pane.
Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[Greg_Deckler]

@Anonymous In theory you could create a measure like:

Measure =
  IF(
    MAX('Table'[Category] = "System2, System1",
    BLANK(),
    COUNTROWS(FILTER(ALL('Table'), [Category] = "System1, System2" || [Category] = "System2, System1"))
  )