Pull data from one table to another without relationship

[Krishna_Newuser]

My report will refresh every Monday (Only) which will have the previous week data (27th Oct to 2nd Nov).  I have two tables where I need to copy the data from one table to another. Note: There is no relation between these two tables.

Need to take the values of PTT column from Walkie Talkie usage Table to Adoption Average Table.
PTT showed using the below Measure in the Walkie Talkie Usage,

PTT =

Var A = CALCULATE (DISTINCTCOUNT (WalkieTalkieUsage [Conversation]))

Var B = CALCULATE (COUNT ('Select Store'[Store No]), ALL('00_SelectDateRange'[Day]))

RETURN

A/B

When I try to pull the PPT to Adoption Average using the below measure, its pulling only 40+ records, original data available is 502. Please help on this.

PPF = LOOKUPVALUE(Adoption_Average[StoreNo], Adoption_Average[StoreNo], WalkieTalkieUsage[PTT])

Adoption_Average

StoreNo	Store Name	Txn Date	Adoption %	P2T
1	AA	27-10--2024	100%	502
2	AB	27-10--2024	97	2011
3	AC	27-10--2024	98	532
4	BB	27-10--2024	55	2945
5	CC	27-10--2024	66	34
6	DD	27-10--2024	77	2089
7	EE	27-10--2024	44	509
8	RR	27-10--2024	93	358
9	TT	27-10--2024	89	554
10	GG	27-10--2024	55	1047
12	HH	27-10--2024	33	2797
312	II	27-10--2024	67	444

StoreNo	Store Name	Txn Details	Adoption %	P2T
1	AA	27-10--2024	100%	502
2	AB	27-10--2024	97	2011
3	AC	27-10--2024	98	532
4	BB	27-10--2024	55	2945
5	CC	27-10--2024	66	34
6	DD	27-10--2024	77	2089
7	EE	27-10--2024	44	509
8	RR	27-10--2024	93	358
9	TT	27-10--2024	89	554
10	GG	27-10--2024	55	1047
12	HH	27-10--2024	33	2797
13	II	27-10--2024	67	444
14	NN	27-10--2024	100	333
15	RT	27-10--2024	55	509
16	FG	27-10--2024	33	358
17	ERT	27-10--2024	67	554
18	FWZ	27-10--2024	77	358
19	YU	27-10--2024	44	359
20	TBN	27-10--2024	93	360
21	FEV	27-10--2024	89	361

[Anonymous]

Hi @Krishna_Newuser 

Thank you very much Ankur04 and grazitti_sapna for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

“Adoption Average”

Make sure you have calculated the PTT in the Walkie Talkie Usage table. you can use your previous DAX formulas.

PTT =
Var A = CALCULATE (DISTINCTCOUNT (WalkieTalkieUsage [Conversation]))
Var B = CALCULATE (COUNT ('Select Store'[Store No]), ALL('00_SelectDateRange'[Day]))
RETURN
A/B

Please create a measure and I modified your code:

Adoption_PTT = 
AVERAGEX(
    FILTER('Walkie Talkie Usage', 'Walkie Talkie Usage'[StoreNo] = SELECTEDVALUE('Adoption Average'[StoreNo])
    ),
    'Walkie Talkie Usage'[PTT]
)

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.

[Krishna_Newuser]

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. 

[Anonymous]

Hi @Krishna_Newuser 

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.

[Krishna_Newuser]

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

[Anonymous · Accepted solution]

Hi @Krishna_Newuser 

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.

[Krishna_Newuser]

This is Great, Thank you so much. It works 🙂

[Ankur04]

Hi @Krishna_Newuser ,

Can you please share some sample data and expected output. it will be helpful for us to solve your query in short time and in right way.

Thanks,

[grazitti_sapna]

Hi @Krishna_Newuser,

In Power BI, pulling data between tables without relationships, especially when there’s a mismatch in record count, can be challenging. To achieve this more reliably, consider the following approach that uses aggregation instead of LOOKUPVALUE, which may only retrieve a partial set due to its single-value constraint.

Solution: Using a Calculated Measure in Adoption Average

To bring over the PTT calculation, create a measure in the Adoption Average table that references the Walkie Talkie Usage table.

• Define PTT Calculation: Ensure your original PTT measure is correct in the Walkie Talkie Usage table:
  
  DAX
  Copy code
  PTT =
  VAR A = CALCULATE(DISTINCTCOUNT(WalkieTalkieUsage[Conversation]))
  VAR B = CALCULATE(COUNT('Select Store'[Store No]), ALL('00_SelectDateRange'[Day]))
  RETURN A / B
• Create a New Measure in the Adoption Average Table: Use this measure to bring over the PTT calculation without LOOKUPVALUE.
  
  DAX
  Copy code
  PTT_In_AdoptionAverage =
  CALCULATE(
      [PTT],
      ALL('Adoption Average')  -- This removes row context filtering in Adoption Average
  )
• This formula calculates PTT as defined in Walkie Talkie Usage and makes it available in Adoption Average without filtering down to individual rows.
• Note: Replace [PTT] with the fully qualified name if your PTT measure isn’t directly referenced.
• Map Results to Store No, if Required: If your goal is to calculate PTT based on each StoreNo or match both tables more granularly, consider creating a table or matrix visualization to show PTT alongside Adoption Average based on common attributes like StoreNo.

Troubleshooting if the Above Fails

If CALCULATE doesn’t yield the full record set, test if cross-joining StoreNo with PTT values from both tables in a calculated table can help instead. Let me know if you'd like details on that approach.

If I have resolved your question, please consider marking my post as a solution. Thank you!