Counting Concatenated Fields

[ZoeM]

Hello Community.

I am trying to count the number of concatenated fields within the data set and show that figure s a KPI. I am using the below expression:

Count( Concat(DISTINCT Gateways&[Issue ID]) )

But I am getting an error. 

Any help rendered will be appreciated.

Thank you.  

[sunny_talwar]

May be you need this

Count(DISTINCT Gateways & [Issue ID])

or this

SubStringCount(Concat(DISTINCT Gateways&[Issue ID], ', '), ', ') + 1

[ZoeM]

Thanks for the response Sunny.

The first response works in a straight table, however, I am developing a KPI and it did not work. The main issue I am facing is duplicates and want to focus on distinct items only hence the need to concat. 

I tried the second expressions and I did not get the anticipated result. 

[sunny_talwar]

What is your dimension in the straight table where this worked? May be you need something like this

Sum(Aggr(Count(DISTINCT Gateways & [Issue ID]), Dimension/s))

[ZoeM]

So I got the second expression to work. However, it is counting all the results and I need to count only for the Dimension Issue Ctegory = 'Contained'...

=SubStringCount(Concat(DISTINCT Gateways&[Issue ID], ', '), ', ') + 1 

Can I add that clause somewhere within the expression?

[sunny_talwar]

May be this

=SubStringCount(Concat(DISTINCT {<[Issue Category] = {'Contained'}>} Gateways&[Issue ID], ', '), ', ') + 1 

[ZoeM · Accepted solution]

Can you help me understand how you did this? because it is amazing...you have no idea how long I have struggled with this. 

[sunny_talwar]

Glad I was able to help