Counting Occurrences

[Asking]

Hi,

I have a table that is filtered on the dashboard based on ID:

ID Value

1   X

1   Y

2    X

2   Z

3   X

3   Y

if I used the function CONCATENATEX I can get a table like this

1 X,Y

2 X,Z

3 X,Y

What if I want to create a visual with the # of occurances of the concatenated value, meaning:

X.Y  2

X.Z  1

Thanks!

[v-xinruzhu-msft · Accepted solution]

Hi @Asking  ,

You can create a new column in table, the code is as follows:

Column = CONCATENATEX(FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])),'Table (2)'[Value],",")

Then create a new table

Table 2 = var a=SUMMARIZE('Table (2)','Table (2)'[ID],'Table (2)'[Column])
 var b=SUMMARIZE(a,[Column])
 return ADDCOLUMNS(b,"count",COUNTAX(FILTER(a,[Column]=EARLIER('Table (2)'[Column])),[Column]))

^{Best Regards,}

^{Yolo Zhu}

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

[Asking]

Thank you.

However, how do I create the actual table and not only get the scalar value please?

[Greg_Deckler]

@Asking Try this:

Measure = 
    VAR __Table = ADDCOLUMNS(SUMMARIZE('Table',[Column3]),"__Text",CONCATENATEX('Table',[Column2],","))
    VAR __Values = DISTINCT(SELECTCOLUMNS(__Table,"__Text",[__Text]))
    VAR __Table1 = ADDCOLUMNS(SUMMARIZE('Table',[Column1]),"__Text",CONCATENATEX('Table',[Column2],","))
    VAR __Table2 = FILTER(__Table1,[__Text] IN __Values)
    VAR __Result = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table2,"__Index",[Column1])))
RETURN
    __Result