Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
2 years ago

composite model inner join

i want to show blank row with 2 cases count as shown below in composite mode. I am able to see this blank row in case of import and direct query however this blank row is not appearing in case of composite model and it seems Power BI is doing inner join. Please see sample data set and pbix file in import mode for reference 

 

sample dataset: 

https://docs.google.com/spreadsheets/d/1JqSbUxmuynZkSi-jF7--zoMakju3eQ1y/edit?usp=drive_link&ouid=107010240500343952141&rtpof=true&sd=true

 

sample pbix :

https://drive.google.com/file/d/1cSgRKXl5d9sbh-IAz6UaNEjsL0JMUv2j/view?usp=drive_link

 

Expected Results using Composite Mode:

 

 

Current results using Composite Mode:

 

Results using both tables in Direct Query mode:

 

Results using both tables in Import mode :

 

Sample Data Set:

Case table:

 

Group Table:

 

 

 

 

Need Help

5 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    2 years ago

    The only scenario that comes to my mind is when using composite models , Power BI aims to optimize performance so it pushes the join operations down to the data source  The data source, in turn, typically performs an inner join unless specified otherwise.

    In your case,  you have a table in DQ mode and another in import mode, Power BI tends to perform an inner join between these tables, especially when there is a relationship defined between them. This inner join excludes rows from the Case table where the groupid is blank that's why you don't see them.

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

     powerbiexpert22 

    Have you observed the SQL queries that your visual sending to the DB? Since your dimension table is in import mode I guess your visual is sending one unique SQL query for each unique groupid. You can watch this video for more info: https://www.youtube.com/watch?v=TgAXt1ifdhs

     

    If possible, keep your import mode tables to Dual mode and check. 

     

    Just out of curiosity, have you enabled 'Show items with no data'?

     

     

    Need a Power BI Consultation? Hire me on Upwork

     

    Connect on LinkedIn

     




    Did I answer your question? Mark my post as a solution!
    If I helped you, click on the Thumbs Up to give Kudos.

    Proud to be a Super User!

     

    • powerbiexpert22's avatar
      powerbiexpert22
      Impactful Individual
      2 years ago

      Hi tharunkumarRTK ,

       

      it works with dual mode however i do not want to make all my dimension tables in dual mode

      please see below dax generated

       

      DEFINE
          VAR __DS0Core = 
              SUMMARIZECOLUMNS(
                  ROLLUPADDISSUBTOTAL('group'[desc], "IsGrandTotalRowTotal"),
                  "Total_Cases", 'cases'[Total Cases]
              )

          VAR __DS0PrimaryShowAll = 
              ADDMISSINGITEMS('group'[desc], __DS0Core, ROLLUPISSUBTOTAL('group'[desc], [IsGrandTotalRowTotal]))

          VAR __DS0PrimaryWindowed = 
              TOPN(502, __DS0PrimaryShowAll, [IsGrandTotalRowTotal], 0, 'group'[desc], 1)

      EVALUATE
          __DS0PrimaryWindowed

      ORDER BY
          [IsGrandTotalRowTotal] DESC, 'group'[desc]