PowerBI Matrix how to hide blank rows

[HunTran]

Hi,

I have a data model that looks like this: Sales are linked to Stores by a LocationKey, also Sales are linked to RSA by an RSAKey.

I need to create a matrix that shows Stores, then RSA, then the Sales$.

But because there is no direct relationship between Stores and RSA, when I drill down, all RSA will show. Is there any way that I can get rid of the blank rows?

(1 solution that I used before is to create Related Columns of Stores Name and RSA Name in the Sales table, and drop that in the matrix, that way it will show without blank rows, but I do not want to do Related Columns because Sales table get really big (>20Mil rows) )

Appreciate any insights. Thank you guys very much,

[parry2k]

@HunTran in the matrix visual image you shared, I see lot more values in that, what all are values you are using in the matrix visual?

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️

[HunTran]

The visual i am using is something like this:

Sales$ = CALCULATE(SUM(HomeStore[Sales]),DateMaster[FiscalYear] = YEAR(TODAY()))

Does the additional year filter affect it?

(To clarify, the LocationKey coming from Location table, RSACode coming from RSA table, not the Sales table. it works automatically if both of them come from the Sales table)

[parry2k]

@HunTran what measure you are using to visualize the data? It should work out of the box.

👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️