Forum Discussion
Helper IDAX Filter Issue with Tables connected with relationships
Hello there,
Let's think about couples (1 male & 1 female) who are going to make sport together.
I have got two tables.
The first table contains "Timestamps" when the females did sport. Columns:
- Timestamp ID
- Female name
- Timestamp
The second table contains "Pairings" of the couples, with start time of the partnership and end time of the partnership. Columns:
- Pairing ID
- Female name
- Male name
- Start
- End (may be null which meens the partnership is still alive)
Now I want to create a column "Male name" in the first table. At first the female Names have to match in both tables. Then the correct Male name should come out of the pairing for which the Timestamp from the first table is between Start and End of the pairing.
Every female and every male is only in one (or zero) parnerships at one time ;-). This meens that there will be timestamps for which the male name should be "null" because the female is not in a partnership for this timestamp.
I tried some things with
MailName =CALCULATE( Pairings[MaleName];FILTER(...)
but since I'm quite new to Power BI and DAX I'm totally stuck.
May somebody help and give the rights hints, functions or formulas?
Best regards
JJ
2 Replies
- TomMartens
Super User
Hey,
I have to admit, that I did not look at a data model that promised to reveal as much insights about the human nature than your data model, even if I guess I do not believe in all the underlying assumptions that I mean to discover in your model.
Nevertheless, the DAX, that's why we are here.
Create a calculated column in your female table (wish I had one, in one of numerous data models) like soMaleName = var femaleTimeStamp = 'femaleTable'[TimeStamp] return CALCULATE( FIRSTNONBLANK('maleTable'[MaleName],1) ,FILTER(ALL('maleTable') ,'maleTable'[Start] <= femaleTimeStamp && 'maleTable'[End] >= femaleTimeStamp ) )
Hope this gets you started (funny phrase)
Regards
Tom
