Count of interconnected fields in chart script

[MS234]

Hi,

Having below dataset:

ColumnA: g, h,  c

ColumnB: e, e, f

I am trying to calculate all rows, which are unique records. Exemplary, if record 'e' is repeated in ColumnB, but has unique value in ColumnA, it should be counted as a separate record. 

Result should return 3 from above set.

I have so far tries set analysis and distinct counts without bigger success.

Did any of you have similar issue?

[Andrei_Cusnir · Accepted solution]

Hello,

In this use case scenario, perhaps you might need to use Aggr() function. For example I have the following data set:

According to your requirements, you want to count the ColumnB and as an output get the number "5". Because:

So I have created an KPI and used the expression: Sum(Aggr(Count(DISTINCT ColumnB), ColumnA))

This expression will count DISTINCT values of ColumnB field based on ColumnA field and then will sum all the counts together.

As you can see it has counted 5 as expected

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.