Redundant, mostly empty rows upon Matrix drilldown

Question

I have set up a simple matrix to track bond values as their prices change. The fact table includes the bond purchase quantity and original price, along with the account holding the asset and the purchase date(s). The dimension table updates current prices, and is linked one-to-many via the CUSIP symbol for the bond. The resulting matrix heirarchy, with increasing granularity, is Symbol->Account->Purchase Date. Values displayed across the top of the matrix are Description, Quantity, Current Price, Current Value, and Gain/Loss

Before the drill-down the matrix displays the correct price and aggregate amounts, but when I drill down the matrix produces an individual row for each account in the entire file, and each purchase date in the entire file, rather than only those accounts that contain the bond and the purchase date(s) for each particular bond. These many "blank" rows contain only the current price linked from the dimension table.

I am pretty new to power BI, so will not be surprised if the answer shows my naivete, but any guidance to determine what I need to do differently will be most appreciated. p.s.-- I tried to copy over relevant snippets from the power BI display, but was unable to get this message pallette to paste them.

Many thanks

Bill

greg_deckler · Answer

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

v-juanli-msft · Answer

Hi&nbsp;Anonymous&nbsp;
Firstly, let me confirm with you as follows:
What i get from your statement:
1. you have two tables: fact table, dimension table,
&nbsp;&nbsp; their relationship is fact table( many) to dimension table( one) based on column [symbol].
2. you create a matrix, add columns [symbol], [account], [purchase date] to the "Row" of the matrix,&nbsp;
&nbsp;&nbsp; add [Quantity],[Current price],,ect in the "Value" of the matrix.
3. finally, your matrix show many blank rows when drilling down to "Account" or "Purchase date".
&nbsp;
If there is any wrong, please correct me.
In addition, please show some screenshots, or give your expected results.
&nbsp;
Best Regards
Maggie
&nbsp;

anonymous · Answer

First thank, and apologies for the unusually large font size in my original message.... not sure how that happenedYou have accurately summarized the premises.&nbsp;Below are renderings of the matrix with and without drilldown.&nbsp; It is clear that the linked [Price Now] measure from the dimension table ('Current Prices and Yields') is causing all these invalid rows to be generated. &nbsp; Here is the measure formula I've used, which features the HASONEVALUE expression to suppress a summed total in the price field.&nbsp;Price Now = IF(HASONEVALUE('Current Prices and Yields'[Price]),VALUES('Current Prices and Yields'[Price]))I read a post that hinted to a possible solution wherein the VAR and IF expressions could be used to require blank prices in any row where the quantity is blank, but what concerns me is my lack of understanding the logic that is driving the linked price to generate all these rows.&nbsp; If I've erred in creating the formula, I'm hoping there is a simpler solution.&nbsp;Many thanksBill&nbsp;Rendering without DrilldownSymbolDescriptionQuantityPrice NowValue NowAmount InvestedTotal Gain/Loss04109KDC8ARKANSAS ST DEV FIN AUTH CAP IMPT REV, REVOLVING LN FD REV BDS, SER 2011C$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300$ 101.70$&nbsp;&nbsp;&nbsp;&nbsp; 30,510$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,006$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 504&nbsp;Rendering WITH drilldown&nbsp;SymbolAccountPurchase DateDescriptionQuantityPrice NowValue NowAmount InvestedTotal Gain/Loss04109KDC8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AcctXYZ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/13/2012&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10/16/2012&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10/18/2010&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;11/21/2011ARKANSAS ST DEV FIN AUTH CAP IMPT REV, REVOLVING LN FD REV BDS, SER 2011C$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 300$ 101.70$&nbsp;&nbsp;&nbsp;&nbsp; 30,510$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 30,006$&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 504&nbsp;&nbsp;12/1/2005&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12/15/2011&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12/5/2011&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2/8/2017&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/24/2016&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/6/2015&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/9/2012&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5/2/2018&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5/8/2013&nbsp;&nbsp;$ 101.70&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Redundant, mostly empty rows upon Matrix drilldown

3 Replies

Related content

Snowflake Mirroring to Fabric mostly does full loads

Handle AverageX on different granularities matrix drilldown

Drilldown Choropleth Gone???

How to expand record without mostly null values

Drilldown Table Help

Symbol	Description	Quantity	Price Now	Value Now	Amount Invested	Total Gain/Loss
04109KDC8	ARKANSAS ST DEV FIN AUTH CAP IMPT REV, REVOLVING LN FD REV BDS, SER 2011C	$ 300	$ 101.70	$ 30,510	$ 30,006	$ 504

Symbol	Account	Purchase Date	Description	Quantity	Price Now	Value Now	Amount Invested	Total Gain/Loss
04109KDC8
	AcctXYZ
		1/13/2012			$ 101.70
		10/16/2012			$ 101.70
		10/18/2010			$ 101.70
		11/21/2011	ARKANSAS ST DEV FIN AUTH CAP IMPT REV, REVOLVING LN FD REV BDS, SER 2011C	$ 300	$ 101.70	$ 30,510	$ 30,006	$ 504
		12/1/2005			$ 101.70
		12/15/2011			$ 101.70
		12/5/2011			$ 101.70
		2/8/2017			$ 101.70
		3/24/2016			$ 101.70
		3/6/2015			$ 101.70
		3/9/2012			$ 101.70
		5/2/2018			$ 101.70
		5/8/2013			$ 101.70