Blending is done on Product and Date both.

We have dragged Product from Primary data source and rest of the fields like Engaged Visits, Leads, and Visits from Secondary data source

How to get a distinct count of Customers from the secondary data source ?

If you see the secondary data source

A --112,113 & 114 ---->3

B --210 & 211 ------> 2

C--302 & 303 ------> 2

Simple? well not that if you are dealing with secondary datasource :(

You might think of dragging Customer num field to Rows and select COUNTD as below

You will see below error for sure :(

I got this question and I would like to share the way I tried, maybe there are few other ways but then I thought of sharing this way as well.

When I was randomly playing with this workbook I found one interesting point

First of all, convert Customer num to Dimension which makes sense (Uniquely identified)

Now drag Customer num to rows twice (Yes twice)

Convert the second one to measure like below

Now if you notice below 224 =2 * 112, 420=2*210 and so on.

So whenever there are multiple 'Customer num' the Sum will multiply according to that

Create one calculation which will give the count of that Customer (not distinct just count )

Count Customer=

sum([Customer num])/min([Customer num])

Getting closer now , if I convert 'Count Customer' Values as 1 by default and then add them having partition as Product I shall be closer to DISTINCT COUNT OF CUSTOMER / PRODUCT.

For example (see 1st row above) if I can convert 2 to 1 and then add from top to bottom for Product A then it will come as 3. We need to repeat the same for B and C as well.

Here is that magical Calculation that you need to know

**window_sum( if sum([Customer num])/min([Customer num])=1 then 1 else 1 END)**

Also, notice the Compute Using section for the above calculation

**Got it finally :)**

As we all know COUNTD may not be feasible with Secondary datasource at times (see above RHS error), sharing one of the workarounds to sort it. Well, this came to me as a query @ community forum. Hope it helps you as well. Please note that in some of the cases you might find COUNTD working

Primary Data Source

By Ritesh

**How to COUNTD(digit dimension) at secondary datasource(blending) ? **(9-May-18)

+91-9739098474

Secondary Data Source

Feedback form