topic Mechanism of filter propagation in default matrix viz in DAX Commands and Tips

Mechanism of filter propagation in default matrix viz

CNENFRNL — Fri, 16 Oct 2020 13:13:35 GMT

Dear gurus,

As a frequent user of DAX, I understand fundamental mechanism of filter propagation, say from one side to * side along a 1:* relationship, or cross filter via bi-directional relationship, etc.

Nevertherless, I didn't wrap my head around such a seemingly-natual effect of matrix in terms of filter propagation; I can't even pinpoint keywords to google...😅 so I turn to you for help. TIA!

My mockup data model is simple enough

Then I drag columns from dimensional tables dDate[Date], dRep[Rep], dProd[PID] into rows of matrix viz, very natually, the default viz shows like this,

To my understanding, the default viz means dDate filters dRep/dProd; but theoretically, fundamental filter propagation( from 1 to * ) doesn't take effect ( maybe I'm totally wrong ) among these dimensional tables. And what's the mysterious power behind the scene?

Here's the dummy file at your disposal.

Have a nice weekend!

Re: Mechanism of filter propagation in default matrix viz

daxer-almighty — Sat, 17 Oct 2020 14:06:29 GMT

Visuals always use SUMMARIZECOLUMNS behind the scenes, so it means only those combinations that exist in the fact table are displayed in the matrix.

Re: Mechanism of filter propagation in default matrix viz

CNENFRNL — Sat, 17 Oct 2020 19:14:05 GMT

Hi, daxer-almighty , thanks for the response and by your hint, I think I found the article unveiling the mechanism under the hood, auto-exist and empty-rows removal. Here's the link to the article: https://www.sqlbi.com/articles/autoexist-and-normalization

"... two different mechanisms are working, ... auto-exist and empty-rows removal. Auto-exists is a feature working at the server level. Auto-exists makes non-existing combinations of attributes invisible to any client tool that queries your database. Empty-rows removal is a PivotTable feature that hides rows containing only blank values in the measures."

What I tried is, according to the article, a result of empty-rows removal; whereas auto-exist is a more profound and instrinsic mechanism easily to neglect. Thus I amend another matrix in this regard in my dummy file.