Get information from one table to a second table depending on various other tables

[wvuthha]

Hi guys,

I have 3 tables (Tempo_worklog, GITG - CIL, GITG - Hourly rates).

In the first table, timebookings are stored. In the second table there is the company of the employees. In the third table there are rates for every company within different time periods.

I want to create a measure in Tempo_worklog. There I want to get the results of EMP_INS_NAME2 of GITG – CIL and also Hourly rate from GITG – Hourly rates for every row.

My result should look like this

I am completely lost. I think, I need a combination of CALCULATE and FILTER, but I am too stupid to solve this.

I hope anyone can help me here

Best regards

Thomas

[Anonymous]

Hi  @wvuthha ,

Here are the steps you can follow：

1. Create calculated table.

Calculated Table =
SUMMARIZE(
    'Tempo worklog',
    'Tempo worklog'[Author],'Tempo worklog'[Startdate],'Tempo worklog'[Timeworked h],
    "Firm",
    MAXX(FILTER(ALL('GITG-CIL'),'GITG-CIL'[EMP_IDENT]=EARLIER('Tempo worklog'[Author])),[EMP_INS_NAME2]))

2. Create calculated column.

Stundensatz =
var _table1=
SELECTCOLUMNS(FILTER(ALL('Calculated Table'),[Firm]=EARLIER('Calculated Table'[Firm])),"1",[Startdate])
return
IF(
    [Author]="Jane" &&
    'Calculated Table'[Startdate]=MINX(FILTER(ALL('Calculated Table'),'Calculated Table'[Author]=EARLIER('Calculated Table'[Author])),[Startdate]),
MINX(FILTER(ALL('GITG-Hourly rates'),
'GITG-Hourly rates'[Company] = EARLIER('Calculated Table'[Firm]) &&'GITG-Hourly rates'[From] in _table1),[Hourly rate]),
MAXX(FILTER(ALL('GITG-Hourly rates'),
'GITG-Hourly rates'[Company]=[Firm]),[Hourly rate]))

3. Result:

Best Regards,

Liu Yang

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

[daXtreme]

@wvuthha 

I like your self-criticism.. 🙂 "... but I am too stupid to solve this." Well, you may be too harsh on yourself. We are all born too stupid for anything. It just takes time the become wiser. Practice makes perfect.

If you need a calculated table, then here it is:

Calculated Table = // This is not a measure!
// Please change the horrible names of the tables,
// the names should be easily pronounceable. Same goes for
// field names.
//
// The model needs to have these relationships:
// 'citg - cil'[emp_ident] 1 ->>- * Tempo_worklog[Author]
// 'citg - hourly'[Company] 1 ->> * 'citg - cil'[emp_ins_name 2]
SELECTCOLUMNS(
    SUMMARIZE(
        Tempo_worklog,
        Tempo_worklog[Author],
        Tempo_worklog[Startdate],
        Tempo_worklog[Timeworked h],
        'ctig - cil'[emp_ins_name 2],
        'citg - cil'[Hourly rate]
    ),
    "Author", [Author],
    "Startdate", [Startdate],
    "Timeworked h", [Timeworked h],
    "Firma", [emp_ins_name 2],
    "Stundensatz", [Hourly rate]
 )

[wvuthha]

Thank you for your help. Sometimes I despair, when I exactly know, what I want to do, but I am not able to solve it in Power BI 😉

In your comment you mentioned, that GITG-Hourly rates and GITG-CIL must have a 1:n relationship. This is what I don't have.

In GITG-CIL for every employee there is exactly one row. But in GITG-Hourly rates, I have n rows for every company because they can change their hourly rates.