Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Count uniqe values existing in two variable tables

Hi, I have two connected tables.

 

The first one (Table1) cointains sales data with unique client ID and date among other things. The second one (Table2) is a simple table with dates.

I'm trying add a new column to the second table, where for each date there is a percentage of returning clients from the previous month. Here is the code:

 

ReturningCustomers=

Var startDate = /// (logic counting beginning of the last month)

Var endDate = ///  (logic counting end of the last month)

Var lastMonth = CALCULATE( DISTINCTCOUNT(Table1[ClinetID]); FILTER( Table1; [Date]>=startD && [Date]<=endD ) )

Var lastMonthColumn = CALCULATE( DISTINCT(Table1[ClinetID]); FILTER( Table1; [Date]>=startD && [Date]<=endD ) )

Var currMonthColumn = CALCULATE( DISTINCT(Table1[ClinetID]); FILTER( Table1; [Date]=Table2[Date] ) )

Var X = ??? (number of unique values existing in both lastMonthColumn and currMonthColumn)

 

Return

X/lastMonth

 

The problem is I don't know how to find number of unique values existing in both tables - variable X?

 

Sample data

Table1:

ClientIDDate
A01.01.2020
B15.01.2020
C30.01.2020
A02.02.2020
B02.02.2020
D06.02.2020

 

Table2:

Date
31.01.2020
29.02.2020

 

The expected result is:

DateReturning customers
31.01.2020...
29.02.202067%

So 2/3 clients returned

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks a lot!

       

      I don't know if you meant Countrows(union(table1,table2)) or Countrows(union(currMonthColumn, lastMonthColumn))

      But the second one worked as expected 😁

      • Anonymous's avatar
        Anonymous
        Not applicable

        I have given generalized answer so have to replace it by using existing table or virtual tables which you stored in visual.

         

        You replaced it correctly.

         

        Thanks & regards,
        Pravin Wattamwar
        www.linkedin.com/in/pravin-p-wattamwar

        If I resolve your problem Mark it as a solution and give kudos.