How do we do the complex joins in PBI

[pchekuru]

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)

[itcloud_Learn]

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

[AnalyticsFreak]

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]

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

[pchekuru]

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. 

[JoyCornerstone]

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.