Forum Discussion
Help in filtering data
Dear community,
I'm using a set of data from customers that i upload in a SQL database and I need help to analyse.
I'd like to use the data of customers if they use at least 2 differents products (column names ID_product) to get a straight comparison between these 2 products.
And i'd like also to use these data only if they used the product 5 times (5 differents rows in my table).
Example, if:
Customer1 : use the 2 products 6 times in the year
Customer2 : use only 1 product 10 times in the year
Customer3 : use the 2 products 3 times in the year
I only want to see (and analyse) data from customer1 ... i hope it's clear enought 😅
Hi,
Create a calculated column to count the number of distinct products each customer has used:
ProductCount = CALCULATE( DISTINCTCOUNT(CustomerData[ID_product]), ALLEXCEPT(CustomerData, CustomerData[CustomerID]) )
Create another calculated column to count the total number of usages for each customer:
UsageCount =
CALCULATE(
COUNT(CustomerData[UsageDate]),
ALLEXCEPT(CustomerData, CustomerData[CustomerID])
)Create a calculated column to identify customers who meet both criteria (using at least 2 different products and having at least 5 usages):
IsQualified =
IF(
[ProductCount] >= 2 && [UsageCount] >= 5,
1,
0
)You can now use this IsQualified column to filter your visuals. For example, if you are creating a table or chart, you can add a visual-level filter to only include rows where IsQualified is 1.
1 Reply
Hi,
Create a calculated column to count the number of distinct products each customer has used:
ProductCount = CALCULATE( DISTINCTCOUNT(CustomerData[ID_product]), ALLEXCEPT(CustomerData, CustomerData[CustomerID]) )
Create another calculated column to count the total number of usages for each customer:
UsageCount =
CALCULATE(
COUNT(CustomerData[UsageDate]),
ALLEXCEPT(CustomerData, CustomerData[CustomerID])
)Create a calculated column to identify customers who meet both criteria (using at least 2 different products and having at least 5 usages):
IsQualified =
IF(
[ProductCount] >= 2 && [UsageCount] >= 5,
1,
0
)You can now use this IsQualified column to filter your visuals. For example, if you are creating a table or chart, you can add a visual-level filter to only include rows where IsQualified is 1.