Forum Discussion
Pull data from one table to another without relationship
Hi Anonymous
Ensure that the relationship between the two tables is properly established.
Try this:
Create a column.
Unique Conversation ID Count 1 = CALCULATE( DISTINCTCOUNT('Table 1'[Conversation ID]), ALLEXCEPT('Table 1', 'Table 1'[Store No]) )or
Unique Conversation ID Count 2 = CALCULATE( DISTINCTCOUNT('Table 1'[Conversation ID]), FILTER( ALL('Table 1'), 'Table 1'[Store No] = EARLIER('Table 1'[Store No]) ) )Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
I cannot share the file as my system wont be able to share the file.
Apologies for delay in response!!!
Here, Table 1 will have 46L data where i just need to calculate the Distinct Count of the Conversation ID based on the Store ID, Measure(Updated in the query) worked for me but it would be helpful if Calculated column DAX helps to count to updated the Distinct Count in the Expected Result.
Table 1:
| User ID | Channel ID | TxnDt | Store Name | Store No | Conversation ID |
| ABCD | 197-20012-562 | 10-Nov-24 | AA | 1 | DCBA |
| ABCD | 197-20012-562 | 10-Nov-24 | AA | 1 | DCBA |
| JJKL | 197-20012-562 | 10-Nov-24 | AA | 1 | NNMC |
| NJHJ | 197-20012-562 | 11-Nov-24 | AA | 1 | PPOQ |
| CCHQ | 197-20012-562 | 11-Nov-24 | AA | 1 | FPNQ |
| FURTH | 197-20012-562 | 12-Nov-24 | AA | 1 | LMNN |
| FURTH | 197-20012-562 | 12-Nov-24 | AA | 1 | LMNN |
| HJUK | 197-20012-562 | 12-Nov-24 | AA | 1 | TTNU |
| QTH | 197-20012-562 | 13-Nov-24 | AA | 1 | GHJ |
| KJL | 197-20012-562 | 13-Nov-24 | AA | 1 | NTH |
| PQER | 197-20012-562 | 14-Nov-24 | AA | 1 | EFG |
| YHJ | 197-20012-562 | 15-Nov-24 | AA | 1 | BBCC |
| YHJ | 197-20012-562 | 15-Nov-24 | AA | 1 | BBCC |
| ABCD | 197-20012-562 | 16-Nov-24 | AA | 1 | UHJ |
| ABCD | 197-20012-562 | 16-Nov-24 | AA | 1 | EFG |
| NNMN | 198-20012-763 | 10-Nov-24 | AB | 2 | IIJ |
| JKLM | 198-20012-763 | 10-Nov-24 | AB | 2 | PKM |
| TYPL | 198-20012-763 | 11-Nov-24 | AB | 2 | NSL |
| TYPL | 198-20012-763 | 11-Nov-24 | AB | 2 | MMKL |
| TYPL | 198-20012-763 | 11-Nov-24 | AB | 2 | HJL |
| NNMN | 198-20012-763 | 12-Nov-24 | AB | 2 | TSL5 |
| NNMN | 198-20012-763 | 13-Nov-24 | AB | 2 | TSL89 |
| HAR | 198-20012-763 | 13-Nov-24 | AB | 2 | NQTL |
| FQRS | 198-20012-763 | 14-Nov-24 | AB | 2 | ULTY |
| FQRS | 198-20012-763 | 14-Nov-24 | AB | 2 | ULTY |
| FQRS | 198-20012-763 | 14-Nov-24 | AB | 2 | WQGH |
| NNMN | 198-20012-763 | 15-Nov-24 | AB | 2 | FGHK |
| NNMN | 198-20012-763 | 16-Nov-24 | AB | 2 | TQK |
| NNMN | 198-20012-763 | 16-Nov-24 | AB | 2 | TQK |
Table 2:
| Store Name | Store No | Adoption % |
| AA | 1 | 100% |
| AB | 2 | 99% |
| AC | 3 | 22% |
| BB | 4 | 33% |
| BA | 5 | 54% |
| CA | 6 | 63% |
| FF | 7 | 100% |
| HH | 8 | 53% |
| MM | 9 | 43% |
| NN | 10 | 37% |
| LL | 11 | 93% |
| BK | 22 | 23% |
| MY | 33 | 100% |
| RT | 44 | 30% |
Expected Result:
| Store Name | Store No | Adoption % | Unique Conversation ID Count |
| AA | 1 | 100% | 12 |
| AB | 2 | 99% | 13 |
| AC | 3 | ||
Hi Anonymous
Ensure that the relationship between the two tables is properly established.
Try this:
Create a column.
Unique Conversation ID Count 1 =
CALCULATE(
DISTINCTCOUNT('Table 1'[Conversation ID]),
ALLEXCEPT('Table 1', 'Table 1'[Store No])
)
or
Unique Conversation ID Count 2 =
CALCULATE(
DISTINCTCOUNT('Table 1'[Conversation ID]),
FILTER(
ALL('Table 1'),
'Table 1'[Store No] = EARLIER('Table 1'[Store No])
)
)
Here is the result.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- This is Great, Thank you so much. It works 🙂