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 ?
Solved! Go to 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
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
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!