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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
pchekuru
Frequent Visitor

How do we do the complex joins in PBI

hi ,

We are moving from OBIEE models to Power bi modles. The scneario i have it infront of me is, how do we do complex joins? We have a join as below to calucalte the cumilative number of registrations for each week. How do i replicate this join in PBI betwee these two tables. I am sure some one would've asked about this, please refer me to that link if possible. Thanks.

 

"Self Service MI".""."GGODSADM"."Dim_ELQR_EVTS_WEEKS_OUT_PTR"."EVENT_SRC_WID" = "Self Service MI".""."GGODSADM"."Fact_ELQR_REGISTRATIONS"."EVENT_WID"
AND

CAST("Self Service MI".""."GGODSADM"."Dim_ELQR_EVTS_WEEKS_OUT_PTR"."WEEK_END_DT" AS DATE) >= CAST("Self Service MI".""."GGODSADM"."Fact_ELQR_REGISTRATIONS"."DERIVED_REG_DT" AS DATE)

5 REPLIES 5
itcloud_Learn
Regular Visitor

Hey Did you got the solution what you were asking i am having the same scernerio. if you got the solution please let me know that would be help ful, i am als having complex join scernerio

If you connect Power BI to OBIEE via BI Connector, you can reuse your OBIEE joins instead of recreating them from scratch in Power BI. 

 

More info in the page below:

https://www.biconnector.com/visualize-obiee-data-in-power-bi/ 

v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @pchekuru ,

I'm not very familar with your code.

Do you want to create the relationship between your tables or calculate cross the tables in Power BI?

If you want to create the relationship in power bi, in most cases you won’t have to do anything, the Autodetect feature can do it for you. Please refer to this document.

If you want to calculate something across the tables, please share the data sample and your desired output so that we could help further on it.

Best  Regards, 

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Please fin the use case below. The idea of below output is to see how many registrations are there in each week and it should be cumilative count. 

 

use_case.JPG

I'm not sure you need a complex join to get this working. If you have a DimDate table in your PowerBI model, you can have a week column there.  Then you can use that "week" column in your output table and then add the EventID (count) to that column. Power BI would properly calculate the number of registrations in a given week if you joined the DimDate.Date and Reg_dt fields.  

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors