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.
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
Hello JayJay,
I found quite interesting what you're trying to do. Is it possible for you to share .pbix file? or at least some screenshots (if calculation works offcourse 🙂 )
Thanks,
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 so
MaleName = 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
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 |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |