How I can create relationship between excel sheet with no same or related column with fact table?

[Aysen]

Hi all,

I have an excel sheet which have loaded that in data modeling, so new table is exist, but for creating relationship between this new table and fact table, thare is not any same column, for example no keys or other columns for connecting them together. I do not have even date column. The excel sheet only has two columns. They are MD and TVD.

Excel sheet is included two column MD & TVD. I need to find Right TVD value when I enter the MD value. Please, refer to my photo. For instance, I have MD=10, so the result should return TVD= 20.

Questions: 1. Should I create relationship between this excelsheet and fact table? If yes , How?

MD value comes from fact table and then I should find the related TVD value in Excelsheet. That means I should go to Excel sheet and find the right TVD value.

[v-yueyunzh-msft · Accepted solution]

Hi,  @Aysen 

According to your description, you want to get the [TVD] in the excel sheet when you enter an the value of the [MD] . And you want to get this value in your visual created by the fact table but two tables has no related columns to create relationship?

For your needs, if you want to input the corresponding value in the Excelsheet, and then get the corresponding [TVD], then we can directly get the corresponding value without creating a relationship.

We can create a "Numeric range":

Then we can create  a measure like this:

Measure = var _enter_value = [Parameter Value]

return CALCULATE( SUM('ExcelSheet'[TVD]) , 'ExcelSheet'[MD] = _enter_value) 

Then we can enter an value in the slicer and we can get the value in the fact visual: 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ritaf1983]

Hi @Aysen 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523