Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
4 years ago

Combine 2 data source (excel) and summarize column

HI I have the table below with different tasks. I created a table  showing CID & Client Name with Task= Greenlight Checks and Task Status Closed. Power BI table:     My problem...
Need Help
SteveHailey's avatar
SteveHailey
Solution Specialist
4 years ago

Hi romovaro. I see in the screenshot of the data model that there is a many-to-many relationship between the tables. How is Power BI to know which USD rate you want it to grab when there are multiple rates? I'd suggest fixing the data model so that the exchange rates are on the "one-side" of a 1-to-many relationship. Then you can easily used the RELATED function to grab the correct rate.  You may need to create a third table, a bridging table. 


This documentation from Microsoft should be helpful: Many-to-many relationship guidance - Power BI | Microsoft Docs

  • romovaro's avatar
    romovaro
    Responsive Resident
    4 years ago

    HI Steve. Agreed. The only number that is 1 to 1 is the CUID. (Unique customer number) and now all the connections between tables are CUID to CUID. THe CID can contain different local CUID.

     

     

    Currently I use the Excel formula SUMIF where I select the list of CID and USD dollars from the SFF File and use the CID list from a Pivot table in the BMPS file as criteria

    =SUMIF('Data Source-SFF'!A:A;'PT-Greenlights'!A35;'Data Source-SFF'!BS:BS)

     

     

     

    • romovaro's avatar
      romovaro
      Responsive Resident
      4 years ago

      HI Steve. Checked the article but still cannot make it. I created a "manual" table in Excel in order to get the total USD of the CID and then  connect the CID from the "Manual CID table" and the BPMS table...but again...it's an extra manual work I need to create instead of an automatially refreshed. Any tip would be welcome. thankls