Merging Inside PowerQuery vs Creating Relationship in Model

[afmcjarre]

I have continually run into an issue in this report where I can only account for rows that have blank data points by merging those tables inside of PowerQuery. If I try to simply create a relationship in the model between the two tables then I will have an entire missing row of data in my table visual as opposed to just some empty cells in that given row. Hopefully this will make more sense with some visual aids....

My Dim table is connected to my fact table by propertyID.  However, data that comes from the fact table MAY OR MAY NOT include data that comes from the Dim table.  In other words, a policy may have a "AOP Deductible, or it may not.  If I don't merge these 2 within PowerQuery, when I create my table visual and I add in "AOP Deductible", suddenly that whole row of disappears rather than just given me a blank cell for "AOP Deductible".

Should I be approaching this differently?

Thanks in advance!  

[v-henryk-mstf · Accepted solution]

Hi @afmcjarre ,

It can be the filtering effect caused by the relationship between multiple tables. Generally speaking, we recommend minimizing the use of bi-directional relationships. They can negatively impact on model query performance.  A Many-to-many relationship means both columns can contain duplicate values. 

You can read the suggestions given in the official document and design the table model reasonably. Then try again.

related document link:

Bi-directional relationship guidance - Power BI | Microsoft Docs

Model relationships in Power BI Desktop - Power BI | Microsoft Docs

If the problem is still not resolved, please provide detailed error information and test the data model (remove sensitive information). Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@afmcjarre , I did not get it complete. Based on what I got. I think you need to see data for dimension elements with no data. For that, you can have a measure with +0

or use this

countrows(Table)+0

[afmcjarre]

Could you share an example of the measure you mentioned? Unfortunately, "Show items with no data" does not work in this example because of the multiple other columns with no data. I don't want them to show.