Counting values and concatenating them

[CarlsBerg999]

Hi, 

I'm using a simple measure in my matrix:

Programs = CONCATENATEX('Project Task ','Project Task'[Program], UNICHAR(10))

The result in the matrix is presented below: 

Week	Category 1	Category 2
WK1	Program A Program A Program B	Program B Program B Program B
WK2	Program A Program A Program B Program B

The problem is the repeatead values in same cell. I don't want DISTINCT values either, because i need to know that there are 2 x of Program A in WK1 / Category 1. What i need is it to show "Program A x 2" 

Goal: 

Week	Category 1	Category 2
WK1	Program A x 2 Program B	Program B x 3
WK2	Program A x 2 Program B x 2

How do i do this in dax? 

[Thejeswar]

Hi @CarlsBerg999 ,

I have modified the DAX that @Greg_Deckler suggested to avoid issues because of NULLs (Blanks).

The Below are the two measures

mymeasureA = 
VAR _count = SUMMARIZE(Table_my, Table_my[Week], Table_my[Category 1], "_count", IF(countrows(Table_my) - COUNTBLANK(Table_my[Category 1]) = 0, BLANK(), countrows(Table_my) - COUNTBLANK(Table_my[Category 1])))
return
CONCATENATEX(_count, IF([_count] > 1, [Category 1] & " x " & [_count], [Category 1]), " 
")

mymeasureB = 
VAR _count = SUMMARIZE(Table_my, Table_my[Week], Table_my[Category 2], "_count", IF(countrows(Table_my) - COUNTBLANK(Table_my[Category 2]) = 0, BLANK(), countrows(Table_my) - COUNTBLANK(Table_my[Category 2])))
return
CONCATENATEX(_count, IF([_count] > 1, [Category 2] & " x " & [_count], [Category 2]), "
")

The Output below

[Greg_Deckler · Accepted solution]

@CarlsBerg999 Maybe:

Programs =
  VAR __Table = SUMMARIZE('Project Task','Project Task'[Program],"__Count",COUNTROWS('Project Task')
RETURN
  CONCATENATEX(
    __Table,
    IF([__Count]>1,[Program] & " x" & [__Count],[Program]),
    UNICHAR(10)
  )