Forum Discussion

wvuthha's avatar
wvuthha
Frequent Visitor
3 years ago

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

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

CALCULATE
Filter

3 Replies

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      wvuthha
      Frequent Visitor
      3 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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