Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Folks,
i'm a PowerBI's newbie , so i think you'll be able to help me in my first steps.
I have two tables, one for employees list of my company, the other for attendance events .
So, employees list has a specific PK but isn't a FK of attendance events.
THE TWO Tables are related by this join condition:
EVENTS.IDEMPLOY = EMPLOYEES.IDEMPLOY AND ( EVENTS.EVENT_DATE BETWEEN EMPLOYEES.INITIAL_DATE AND EMPLOYEES.FINAL_DATE )
I try to use Manage Relationships to make a similar join, but POWERBI dosn't like multiple records for join condition.
I try to make a merge table, but the result isn't i need.
There is anybody can help me?
Simone
example files HERE
Solved! Go to Solution.
Right, the column in your two tables will be calculated differently.
In your Employee table, you would do something like:
JoinColumn = [EmployeeID] & "1"
In your Events column, you would have something like:
JoinColumn = IF([Date]>RELATED(Employees[StartDate]),IF([Date]<RELATED(Employees[EndDate],[Employee] & "1",[Employee] & "0"),[Employee] & "0")
I'd prefer a merge table.
Merge Table = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( EMPLOYEE, EVENTS ), EMPLOYEE[IDEMPLOY] = EVENTS[IDEMPLOY] && EMPLOYEE[INIT_DATE] <= EVENTS[DATE_EVENT] && EVENTS[DATE_EVENT] <= EMPLOYEE[FINAL_DATE] ), "IDEMPLOY", EMPLOYEE[IDEMPLOY], "CATEGORY", EMPLOYEE[CATEGORY], "FINAL_DATE", EMPLOYEE[FINAL_DATE], "FLNUMROW", EMPLOYEE[FLNUMROW], "IDCOMPANY", EMPLOYEE[IDCOMPANY], "IDEMPLOY_ALT_KEY", EMPLOYEE[IDEMPLOY_ALT_KEY], "INIT_DATE", EMPLOYEE[INIT_DATE], "DATE_EVENT", EVENTS[DATE_EVENT], "IDEVENT", EVENTS[IDEVENT], "QTA_EVENT", EVENTS[QTA_EVENT] )
I'd prefer a merge table.
Merge Table = SELECTCOLUMNS ( FILTER ( CROSSJOIN ( EMPLOYEE, EVENTS ), EMPLOYEE[IDEMPLOY] = EVENTS[IDEMPLOY] && EMPLOYEE[INIT_DATE] <= EVENTS[DATE_EVENT] && EVENTS[DATE_EVENT] <= EMPLOYEE[FINAL_DATE] ), "IDEMPLOY", EMPLOYEE[IDEMPLOY], "CATEGORY", EMPLOYEE[CATEGORY], "FINAL_DATE", EMPLOYEE[FINAL_DATE], "FLNUMROW", EMPLOYEE[FLNUMROW], "IDCOMPANY", EMPLOYEE[IDCOMPANY], "IDEMPLOY_ALT_KEY", EMPLOYEE[IDEMPLOY_ALT_KEY], "INIT_DATE", EMPLOYEE[INIT_DATE], "DATE_EVENT", EVENTS[DATE_EVENT], "IDEVENT", EVENTS[IDEVENT], "QTA_EVENT", EVENTS[QTA_EVENT] )
@Eric_Zhang : thank you Eric, i used your solution and every thing works like i need...
Create a column in each table that combines the fields. Then, use those columns to relate your tables.
Ok, i supposed to do it, but there are a variety of dates in event table and thera are only two dates in employees tables.
I which kind i can relate those new columns?
Thanks & Regards
Right, the column in your two tables will be calculated differently.
In your Employee table, you would do something like:
JoinColumn = [EmployeeID] & "1"
In your Events column, you would have something like:
JoinColumn = IF([Date]>RELATED(Employees[StartDate]),IF([Date]<RELATED(Employees[EndDate],[Employee] & "1",[Employee] & "0"),[Employee] & "0")
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |