Forum Discussion

79775722's avatar
79775722
Regular Visitor
4 years ago

Report matrix table left outer join with filter or modify underlying query

Hi, I want a matrix table to show both the parent side columns on the left and the many side columns on the right. 

 

I want to apply filter on the many side to show certain records that meets the filter criteria. If not met, I still want to show the parent record on the left side. Is this possible on the Matrix table visual side without changing the data model by applying filter at the PowerQuery ETL side?

 

by default the matrix appears to use left outer joins, but once filter the applied at the report/component level, it filters out the entire row. Is there a setting to prevent that?

 

Thanks.

Need Help

6 Replies

    • 79775722's avatar
      79775722
      Regular Visitor
      4 years ago

      Hi Amit, I was hoping that would work. Unfortunately, when filter is applied on the many side it still didn't show the one side. 

      would it be possible to pre apply the filter similar in the power query side. Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        HI 79775722,

        Did these blank records not really include in the current table? If that is the case, you can consider creating a table to extract and merge two table category field values and use it as the bridge to link to tables.

        How to Join Many to Many with a Bridge Table in Power BI | Seer Interactive

        After these steps, you can use the new table field as the category/axis to expand not match records.

        Regards,

        Xiaoxin Sheng