Forum Discussion

Kieran-q_20's avatar
Kieran-q_20
Helper I
4 years ago

Relationship not working correctly

Hey everyone, 

I am using a relationshi to replace what was a vlookup in an excel file. However, this time it seems as if the relationship is working incorrectly. There are two tables. Data and DRE. The two tables are linked through a many to one relationship connected by the column Material. In the Data table i created a measure which calculated total cost. This total cost is critical to creating DRE reports. However if i pivot my Excel files exactly the same as my PowerBI Matrix, the Total Cost Data is different. 

If anybody has any tips on how to make the relationship functioning I'd greatly appreciate it,

Thank you  

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Kieran-q_20 , We need to know, how are you using it.

     

    One of the ways to use any column from Data table is to create a measure with DRE

     

    Sumx(DRE, Dre[Qty] * related(Data[Cost Column])

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Kieran-q_20's avatar
      Kieran-q_20
      Helper I
      4 years ago

      Hi, Sorry yes i will clarify further. 

       

      Im hoping to automate an excel process i already have already created. At the moment i have a Data Table and a DRE table. In the excel file, The DRE table currently calculates its costs based on a vlookup pointing towards the DATA table. However, when switching to powerBI  initially i could not create a relationship between the two as neither table's Material values were unique. This is where im sure im going wrong, I Removed all Duplicates from the material column in the Data table. This then allowed me to Link DRE and DATA by material. But Material is not supposed to be unique, Lots of entries can be under the Same Material number. For example The DRE table is called so because it contains "SLOCS" (Naming conventions) that are under the DRE umbrella, these are DRE2, DRE6, NDE2.   so i am sure i am losing data by removing duplicates.  I then created a measure in the DATA table to Replicate the Cost calculation in the DATA table excel file. However i think the problem is the removing of duplicates, Although i need to do so in order to have a relationship. I'm just searching for a way to replace the Vlookup i have in Excel, as to fully automate this process i Do not want the data to have a Vlookup.