cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SniperPro
Frequent Visitor

Join two disconnected table with measure

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_IDOpportunity_NameOpportunity_Status
1Opportunity1Opened
2Opportunity2Closed
3Opportunity3Opened
4Opportunity4Opened

Event Table

Event_IDOpportunity_IDEvent_NameEvent_Date
12Event11/1/2022
22Event21/2/2022
31Event31/3/2022
43Event41/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_NameOpportunity_StatusEvent_NameEvent_Date
Opportunity1OpenedEvent31/3/2022
Opportunity2ClosedEvent11/1/2022
Opportunity2ClosedEvent21/2/2022
Opportunity3OpenedEvent41/4/2022
Opportunity4Opened  

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_NameEvent_DateOpportunity_NameOpportunity_Status
Event11/1/2022Opportunity2Closed
Event21/2/2022Opportunity2Closed
Event31/3/2022Opportunity1Opened
Event41/4/2022Opportunity3Opened

 

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 ?

1 ACCEPTED SOLUTION
MohammadLoran25
Solution Sage
Solution Sage

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

View solution in original post

1 REPLY 1
MohammadLoran25
Solution Sage
Solution Sage

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors