Counting the number of times a word appears in a combined table

[Anonymous]

Hi all,

I got the Gland data from a table called datasheet and the rest of the data from a table called cable schedule and formed a relatioship with its ID. I would like to get the number of times the cable gland appears the combined table below.

eg. A240L    5

      CW40L  3

How can I do that?

Thanks.

[Anonymous]

@Greg_Deckler 

Apologies, I know I have already marked it as a solution but I can't seem to get it right. 

I tried 

VAR tmpCol1 = SELECTCOLUMNS(Datasheet,"Column",[Gland])

VAR tmpCol2 = SELECTCOLUMNS('Cable Schedule',"Column",[ID])

VAR tmpTable = UNION(tmpCol1,tmpCol2)

VAR tmpValue = COUNTX(tmpTable,[Column])

(this doesnt give me the correct values)

and 

VAR Gland = ('Datasheet'[ID]) //get the current value of the thing you are trying to find

VAR Table =

UNION(

SELECTCOLUMNS(ALL('Cable Schedule'),"Column",[ID]),

SELECTCOLUMNS(ALL('Datasheet'),"Column",[Gland])

)

RETURN

COUNTROWS(FILTER(Table,[Column] = Gland))

While this just doesn't work with the error message 'syntax for Table is incorrect' and 'cannot find name 'ID'

 

I have included my field window as well.

Appreciate your help

[Greg_Deckler · Accepted solution]

@Anonymous - If I understand correctly, you want MC Aggregations - https://community.powerbi.com/t5/Quick-Measures-Gallery/Multi-Column-Aggregations-MC-Aggregations/m-p/391698#M129

To elaborate, I'm thinking something like:

Measure =
  VAR __Gland = MAX('Table'[Gland]) //get the current value of the thing you are trying to find
  VAR __Table = 
    UNION(
      SELECTCOLUMNS(ALL('Table'),"Column",[Gland]),
      SELECTCOLUMNS(ALL('Table'),"Column",[Gland 2])
    )
RETURN
  COUNTROWS(FILTER(__Table,[Column] = __Gland))