Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Pull data from one table to another without relationship

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
1 year ago

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!