Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Combine two columns in visual

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 

Need Help
Show and Tell
Tips and Tricks
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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.

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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"))
  )
  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.