Distinct count participates in calculation after filtering

1.1 Expected effect

Count how many supplierids (1,10,11) each product has, using the "Product" table in example. As shown in the figure below:

1.2 Solution

1) Convert "CategoryID" from dimension to indicator to realize de-counting and get the number of categories.

2) Add detailed filtering for the "CategoryID" indicator field. The condition is that "SupplierID" belongs to "1,10,11", and obtain the number of SupplierID [1,10,11] categories.

3) But since each product has a total category, add a counting metric and add "CategoryID+1" to get the number of categories that contain the total category.

4) Finally, add a group table to display the analysis results.

Note: The schedule does not support de-counting.

2. Steps

Example data: the "Product" table in "example".

Objective: Count the number of categories per product.

2.1 Create a dashboard

In "Dashboard", click "New Dashboard", enter related information, and click "OK". As shown in the figure below:

2.2 Add compoent

Make the component using the "Product" table in "example".

Click "Add component", as shown in the picture below:

Select the "Product" table, as shown in the figure below:

2.3 Convert dimension to indicator

Before the dimensions of the component edit interface turn indicators, you need to create a new dashboard and add components using the "Product" table in "example".

After finishing, enter the component editing interface, click "CategoryID", convert it to a indicator, realize the "Counter" count depends on "CategoryID" to get the number of categories. As shown in the figure below:

2.4 Detail filtering

Get the number of categories.

1) Filter "CategoryID" for details, as shown in the figure below:

2) Add filter condition for "CategoryID" : "SupplierID" belongs to "1,10,11." As shown in the figure below:

2.5 Add a calculation indicator

Since each product has a total category, finding the number of categories for a product requires adding one to the "CategoryID" result after adding the filter condition. Add the calculation indicator: CategoryID+1.

1) Add a calculation indicator, as shown in the figure below:

2) Enter formula: CategoryID+1, name it and click "OK". As shown in the figure below:

2.6 Add a group table

Drag the fields of the area to be analyzed into the dimension indicator bar of the analysis area, and uncheck "Show total row" to get the number of each product category. Click "Enter the dashboard" to complete the operation. As shown in the figure below:

Note: Here dimensions are converted into indicators and then added to the calculation indicators for calculation. Although the aggregation function is not used in the formula, the calculation indicators field is still shown as the aggregation function. Since dimensions are converted to indicators, the internal computation logic is equivalent to calculating the decounted counts that depend on the dimensions' fields, using the COUNTD_AGG function.

3. Preview effect

3.1 PC

See section 1.1 of this article for details.

3.2 Mobile

The effect of mobile terminal is as shown in the figure below: