Pull data from one table to another without relationship

Hi Anonymous,

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!

Hi Anonymous ,

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,

Hi Anonymous 

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.