Forum Discussion
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:
| ClientID | Date |
| A | 01.01.2020 |
| B | 15.01.2020 |
| C | 30.01.2020 |
| A | 02.02.2020 |
| B | 02.02.2020 |
| D | 06.02.2020 |
Table2:
| Date |
| 31.01.2020 |
| 29.02.2020 |
The expected result is:
| Date | Returning customers |
| 31.01.2020 | ... |
| 29.02.2020 | 67% |
So 2/3 clients returned
- Anonymous6 years ago
Try this
Countrows(union(table1,table2))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.
3 Replies
- AnonymousNot applicable
Try this
Countrows(union(table1,table2))
Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar
If I resolve your problem Mark it as a solution and give kudos.- AnonymousNot 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 😁
- AnonymousNot 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.