Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User
Super User

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
Super User
Super User

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.