Forum Discussion

nirrobi's avatar
nirrobi
Icon for Helper V rankHelper V
10 years ago
Solved

Can not create visual (table) from 2 related table that connect properly (I think)

Hi all,

 

I face strange situation,

 

I have table for invoices and table for arrival date (both have date column as it not the same date),

I want to create visual (table) that shows the sales id , date of arrival and date of invoice (afterward will create diffrence between those 2 dates) but get error from Power BI Desktop (seems like connection issue).

 

if I try to connect for example, sales id count of item and sum of invoice it work perfectly (exactly the same tables but diffrent colmn, hence I assume the connection is well configure)

 

Can anyone think of any solution.

 

Many thanks in advance.

 

Nir

Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    nirrobi

     

    Based on my test, the problem is caused by the relationship among tables. If the relationship is 1:1,1:1 among the three tables, the original table visual in your pbix would work.

    In your case(many:1,many:1), use two measures instead.

    MAX INV DATE = MAX(INVOICES[INV DATE])
MIN SALES DATE = MIN(SALES[SALES DATE])




    If you have any question, feel free to let me know.

13 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    10 years ago

    nirrobi Can you post some sample data of your tables?

     

    So we can see how the tables are organized and how they are related?

     

    Also do you have a Calendar table?

    • nirrobi's avatar
      nirrobi
      Icon for Helper V rankHelper V
      10 years ago

      Sure,

      Hope it will help.

       

      I have one table for invoices (with duplicate), one table for sales (with duplicate).

      and I create in power bi desktop append query with remove duplicate.

      then I try to connect the sales ID with date of invoice and date of sale and got the attached error

       

       

       

       

      thanks.

       

      • Eric_Zhang's avatar
        Eric_Zhang
        Icon for Microsoft Employee rankMicrosoft Employee
        10 years ago

        nirrobi

        Regarding the error in the picture, the documentation online says

        • You are trying to use two or more fields from tables that are not related. You need to remove the unrelated fields from the visual and then create a relationship between the tables. Once you have done this, you can add the fields back to the visual. This can be done in Power BI Desktop or Power Pivot for Excel. Learn more

        Please try to follow the above recommendation.

         

        By the way, how do you append query with remove dulicate? And why do you connect those tables via date columns? Based on my understanding, those tables are linked by the SALESID.