Count of unique values in one column over another column

Question & Answer
1

Hi there,
Can someone help me how to set up a calculated field that the count of users across devices? I need a separate category for users that have used both browser and tablet. Basis in the below sample dataset.

userID | date | device
A | Jan 1 | browser
A | Jan 2 | tablet
B | Jan 1 | tablet
C | Jan 5 | browser

2

Hi @Christian_Soerup ,
Can you check with this calculated field for

count of users across devices

image

separate category for users that have used both browser and tablet

image

Is it fine @Christian_Soerup or you need something else.

2 Likes
3

Hi @Biswajit_1993 ,
Thanks for your help (again) :wink:

My dataset is however a lot bigger than the sample, I show (it contains some 60.000 unique users). Therefore, the ‘category - calc’ can’t contain specific users. Any other suggestions?

4

I use this as my dataset

image
image648×546 71.9 KB

created this visual
image

created 2 calculated field (you may combine into 1)

  • DeviceCountByUser: distinctCountOver(Device, [UserID],PRE_FILTER) ← to calculate how many type of device purchased by each user
  • DeviceType: ifelse(DeviceCountByUser>1, ‘Browser and tablet’, Device) ← to show the name of Device
4 Likes
5

Hi @royyung ,
Thanks for your solution. Bottom line: your solution does the job. However, my dataset seems to be too large for QuickSight to do the visualization and that’s something I need to work around.
Thanks again :slight_smile: