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)


Secondary Data Source

Feedback form