DAX - Retrieve a value from another unrelated table

user_19845172 0
Viewed 55

I have two tables: 'Events' and 'Occurrences'. In Events, I have the name of the event, start date and end date. In 'Occurrences' I have a date from occurrence, ID occurrence and description of occurrence.

Events Table

Event Start Date End Date
Event 1 01/01/2022 02/14/2022
Event 2 02/15/2022 03/10/2022
Event 3 02/11/2022 03/30/2022

Occurrence Table

ID Ocurrence Occurrence Create Date Description
1 01/10/2022 Foo 1
2 02/11/2022 Foo 2
3 02/20/2022 Foo 3
4 03/20/2022 Foo 4
5 03/30/2022 Foo 5

My Question is: How can I retrieve which event is each occurrence? In this example, the expected result is:

ID Ocurrence Occurrence Create Date Description Event Related
1 01/10/2022 Foo 1 Event 1
2 02/11/2022 Foo 2 Event 1
3 02/20/2022 Foo 3 Event 2
4 03/20/2022 Foo 4 Event 3
5 03/30/2022 Foo 5 Event 3
dax powerbi
1 Answers

add this measure to your table...

Event Related =
VAR _occ =
    SELECTEDVALUE ( 'Table (2)'[Occurrence Create Date] )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Event], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Start Date] <= _occ
                && 'Table'[End Date] >= _occ
        )
    )

add measure as a column

sample

or if you want to add only as a column to the table, not the visual, you can use this calculated column (more or less the same)

Event Related 2 = 
var _occ = 'Table (2)'[Occurrence Create Date] 
  return
    
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Event], 1 ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Start Date] <= _occ
                && 'Table'[End Date] >= _occ
        )
    )
user_52602650
Related