Forum Discussion

Asking's avatar
Asking
Regular Visitor
3 years ago
Solved

Counting Occurrences

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!

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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

     

  • Asking's avatar
    Asking
    Regular Visitor
    3 years ago

    Thank you.

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.