Forum Discussion

SniperPro's avatar
SniperPro
Frequent Visitor
3 years ago
Solved

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 ?

@Dax help
@need help
@powerbi
  • MohammadLoran25's avatar
    MohammadLoran25
    3 years ago

    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

1 Reply

  • MohammadLoran25's avatar
    MohammadLoran25
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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