Pulling data from another table in a many to many relationship

[Netope]

Dear all:

This is what I am trying to do. I have a master table listing project spend from 2018 to 2019. Many projects exist in both years, and also they repeat every month durign the project lifetime, so this table has a LOT of duplicated Project IDs.

Once imported in Power BI I created another table out this master table with only the Project ID as column, of course this table also has duplicated Project IDs:

YEAR TABLE = GROUPBY('Sheet1 (2)';'Sheet1 (2)'[Project ID])

Then I created a many to many relationship (as I have duplciates in both) using the Project ID as Key.

Now I would like to add columns by year, for instance FC 2018, and pull the values from the master table for Projects that are reporting forecast in 2018 only. Projects not having forecast in 2018 should return 0.

This is what I did:

2018 FC TOTAL = LOOKUPVALUE('Sheet1 (2)'[CapEx FC FY];'Sheet1 (2)'[Reporting Month].[Year];2018)

The I got this error message:

"A table of multiple values was supplied where a single value was expected"

I guess this DAX function breaks if there are duplicated Project IDs

Any clue how to do this?

Thanks a lot!

[amitchandak]

Why you still have Many to Many relations.

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Netope]

Everymonth a report is generated showing forecast and actuals related to project spend. We take a picture every month with the data reported in order to later compare ytd, month x vs month y, and so on and so for. Many projects run for more than one month and in some cases for more than one year, so you will see same project IDs everymonth during the project lifetime with different forecast and actuals values.

I can not upload the file, I do not have the option available. I uploaded it to my dropbox here https://www.dropbox.com/s/fegxti2imm1pncm/LookupMany2Many.pbix?dl=0 

Thanks!

[v-chuncz-msft · Accepted solution]

@Netope 

You may use SUMX as follows.

FC 2018 TOTAL =
SUMX (
    FILTER ( RELATEDTABLE ( Planilha1 ), Planilha1[Reporting Month].[Year] = 2018 ),
    Planilha1[FC FY]
) + 0

[Netope]

Thanks a lot! it works