relationships between 2 tables

[FCF]

Hi I have two tables that i cam trying to create a relationship with. each time i set it up with a new column that i have created in both tables i can see some data but not the complete list.

i have creaded a column in both tables called Key1 that has the following creteria.

Key1 = ([Date]&[Week]&[Year])

with this i have set up the relationship, but it is not pulling or displaying properly. any help would be greatly appreciated. thanks

[amitchandak]

@FCF , to me date itself is a key, unless it not aproper date. Also in case, they are transactional table, you should join both with a date table.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Greg_Deckler]

@FCF - Hard to say, what are you seeing? Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[FCF]

hi this is the sample data of what is in the tables

table 1 havatec

when  i select the activity Havatec the column Havetec-Stem/hr should display a value, and it does

the problem is that all activities are displaying the havatec value, and they should be at 0

[v-deddai1-msft]

Hi @FCF ,

What's in your Havetec-Stem/hr ? Would you please show us more details by sample pbix file? It is quitely difficult to figure it out with your description.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[FCF]

hi

this sample may help

1. i have selected the date 07/0//2020

2. Fiscal year F21

3. Activity slicer is Havatec

4.results in the table show correct as follows

date 07/08/20, Emp = Neeson, Matt, pieces = 0 , Waste =0, Hrs= 3.71 ( total hrs on the task havatec) Actitivy = Havatec, Processed = 15813 Havetec-Stems/Hr = 2051.19. this is correct if you devide processed 15813 by Hrs 7.71 the result is 2051.19

the problem is in the relationship, if i change the Activity from Havatec to Picking the column "Processed" and Havetec-Stems/Hr should be 0

same date, but different activity

not that peices, waste have values because they are associated to the activity "Picking"

i have created a relationship column called "key1" that is in all 3 tables

Key1 = ([Date]&[Wk]&[Yr])

[FCF]

i am not clear on how to upload the .pbx file.