Forum Discussion

pchekuru's avatar
pchekuru
Frequent Visitor
7 years ago

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)

Need Help

5 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      pchekuru
      Frequent Visitor
      7 years ago

      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's avatar
        JoyCornerstone
        Icon for Resolver II rankResolver II
        7 years ago

        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.  

  • itcloud_Learn's avatar
    itcloud_Learn
    Frequent Visitor
    3 years ago

    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