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 ,
Thank you for the reply!!!
We could see that count is entirely mismatches, from your screenshot for Store No 1 - count for P2T is 502 but Adoption_PTT is 0.65. Can you help on this.
Hi Anonymous
Can you share the pbix file? As well as your expected results, take care to remove the sensitivity interest.
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 🙂