CODEWITHSUNDEEP
powerbidax
Jar
Jar

Reputation: 13

New Customers after rows filtering

i have used the DAX code below in Power BI (DAX patterns 2015), which works fine to get NEW CUSTOMERS.

Customer is counted as NEW either if he uses Product_1 or Product_2. Now I would like to create the same, but this time related to each product separately. So NEW CUSTOMER PRODUCT_1 measure would be if the client started to use Product_1 for the first time (without any regard to Product_2).

In the data model FACT table I have Column with Product 1 and 2, if in one month was used only Product 2, then the Product 1 is blank in the same row and vice versa. I tried to add Filter(MAIN;MAIN([Product_1]>0)) but it gave me strange results. The new clients repeated whole year, instead of being counted only once.

Absolute_NEW_Customers(PRODUCT_1) = 
COUNTROWS ( 
         FILTER(
                ADDCOLUMNS(VALUES(MAIN[Customer No]);"PreviousSales";
                        CALCULATE(COUNTROWS(MAIN);FILTER(ALL('DateKey');DateKey[Date]<MIN('DateKey'[Date]))));
       [PreviousSales]=0))

Upvotes: 0

Views: 85

Answers (1)

Jar
Jar

Reputation: 13

I have finally found the function that helped. It was necessary to use the "CALCULATETABLE" function by which I filtered the whole table. Below is the final code:

Absolute_NEW_Customers(Product_1) = 
COUNTROWS ( 
FILTER(
       ADDCOLUMNS(
                 CALCULATETABLE(VALUES(MAIN[Customer No]);FILTER(MAIN;NOT ISBLANK(MAIN[Product_1])));"PreviousSales";
                        CALCULATE(COUNTROWS(CALCULATETABLE(MAIN;FILTER(MAIN;NOT ISBLANK(MAIN[Product_1]))));FILTER(ALL('DateKey');DateKey[Date]<MIN('DateKey'[Date]))));
       [PreviousSales]=0))

Upvotes: 1

Related Questions