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

[79775722]

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.

[amitchandak]

@79775722 , based on what I got a measure with +0

or this setting can help

[79775722]

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.

[v-shex-msft]

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

[79775722]

Thanks Xiaoxing. I have done that previously and it works. But I'm trying to avoid doing that. Otherwise I will need to create a bridge table for each type of filter and report. I was hoping that step can be eliminated if the matrix table can preapply filter before its default filter is applied which would have included the blank side.

This also gave me another idea of manipulating just the new bridge table and keep the dimension table the same. I'll play with that to reduce the number of redundant table with different filter. I tried this, but only one active relationship can be enabled.

Thanks.

[v-shex-msft]

HI @79775722,

Perhaps you can try using treatas or userelationship functions to calculate DAX expressions without active relationships:

Power BI USERELATIONSHIP vs TREATAS - Enterprise DNA

Regards,

Xiaoxin Sheng

[79775722]

Thanks, but I'm trying to do this purely with Matrix table with the existing tables. Unless a view like of a table or function can be created?