composite model inner join

[powerbiexpert22]

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=10...

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:

[tharunkumarRTK]

 @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]

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]

[AmiraBedh]

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.

[powerbiexpert22]

Hi @AmiraBedh ,

how to perform left outer join in this case?

[AmiraBedh]

I share the same opinion as @tharunkumarRTK  your visual is sending only one SQL query for each unique groupid.