DAX Filter Issue with Tables connected with relationships

[JensKlöker]

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

[Anonymous]

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,

[TomMartens]

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