Join two disconnected table with measure

[SniperPro]

Hello

On PowerBI, I am connecting with live connection to an as tabular model, I cannot use power query. I .can only create a report level measure:

I have an Opportunity Table and an Event Table that are not connected with a relationship.

Opportunity Table

Opportunity_ID	Opportunity_Name	Opportunity_Status
1	Opportunity1	Opened
2	Opportunity2	Closed
3	Opportunity3	Opened
4	Opportunity4	Opened

Event Table

Event_ID	Opportunity_ID	Event_Name	Event_Date
1	2	Event1	1/1/2022
2	2	Event2	1/2/2022
3	1	Event3	1/3/2022
4	3	Event4	1/4/2022

I want to create a table visual on powerBI then put Opportunity and Event on the columns and it shows the related event and opportunity (like with relationship)

The expected result is:

Opportunity_Name	Opportunity_Status	Event_Name	Event_Date
Opportunity1	Opened	Event3	1/3/2022
Opportunity2	Closed	Event1	1/1/2022
Opportunity2	Closed	Event2	1/2/2022
Opportunity3	Opened	Event4	1/4/2022
Opportunity4	Opened

I want also to have the expected below result when I begin by puting Event on the table visual:(The other direction of the filter)

Event_Name	Event_Date	Opportunity_Name	Opportunity_Status
Event1	1/1/2022	Opportunity2	Closed
Event2	1/2/2022	Opportunity2	Closed
Event3	1/3/2022	Opportunity1	Opened
Event4	1/4/2022	Opportunity3	Opened

Is it possible to create a Measure (or two measures) that I can add to the table visual to do this kind of cross join ?

[MohammadLoran25 · Accepted solution]

Hi @SniperPro ,

Create the measure below and put it into your visuals and set it to 1:

RelationFlag =
SUMX (
    FILTER (
        CROSSJOIN ( OpportunityTable, EventTable ),
        EventTable[Opportunity_ID] = OpportunityTable[Opportunity_ID]
    ),
    1
)

If this answer solves your problem, give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran