Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Merge two tables without missing any rows and without duplicating values from both tables

Hi All,   I have been searching and trying to findout a perfect solution for merging two tables without missing any data from both tables and without duplicating the values but i could'nt get anywh...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    Thanks for your valuable time to spent.

    I did every join to solve this, but i am unable to find out the exact solution that i need.

    But i did solved it by creating another table(MonthYear) which is having the month and date and Mon-Year columns.

    Then i created relationship among these three tables using Date column.

    And when i made a bar cart with plan and actual rev, It is showing the perfect values by keeping Mon-Year of MonthYear table in X-Axis.

     

    Thanks for spending your valuable time and giving suggestions.

     

    If you dont mine, can you please look in this issue please.

    https://community.powerbi.com/t5/Desktop/Need-Help-to-get-resolve-on-multiple-queries-of-Custom-KPI/m-p/175125#M76511

    I ll be so thankful to you.

    MohanV

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi, Anonymous,

 

I guess you certainly have a table of all PMs. So add to the model, and create relationships with actual table and plan table. Then create two measures and a visual table. You can export the data from the visual table.

SumActual =
IF ( ISBLANK ( MIN ( 'ActualTable'[Actual] ) ), 0, SUM ( ActualTable[Actual] ) )

SumPlanned =
IF (
    ISBLANK ( MIN ( 'PlanTable'[Planned Rev] ) ),
    0,
    SUM ( PlanTable[Planned Rev] )
)

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Thanks for the suggestion v-jiascu-msft.

    But still getting the strange values as below.

     

     

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous,

       

      Please check these things below.

      1. What are your formulas of "SumActual" and "SumPlanned"?

      2. What are the values of "SumActual" and "SumPlanned" when you select other PM?

      3. Please check the relationship of those tables. 

      3. Please post a little sample in text type if it's convenient with you.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Anonymous

         

        Hi Mohan V,

         

        Did you solve your problem? What else can I do for you?