Hello there,
let's imagine there are couples (1 male & 1 female) which are doing some sport together.
I have got two tables. The first table "timestamps" contains timestamps at which the female did sport (without being necessary in partnership). This table contains the columns:
- TimestampID
- Timestamp
- FemaleID
The second table "partnerships" contains some partnerships with a start and an end of the partnership and ofcourse the FemaleID and the MaleID. So the columns are:
- ParntershipID
- FemaleID
- MaleID
- Start
- End (may be "null", which meens it is still "active")
What I want to do is to create a column "MaleID" in the first table. So in the first step I filter those partnerships, where the FemaleIDs in both tables match. In the second step I have to proove, which is the valid partnership for this case. So the timestamp has to be between Start and End.
Some rows may be "null" in this new column, because the girls may not be in a partnership for specific timestamps.
I tried some things with
CALCULATE(Partnerships[Males];Filter(...))
but since I'm new to Power BI and DAX, I'm totally stuck at this point. May somebody help with the right hints, functions or formulas?
Best regards!
JayJay
