Redundant, mostly empty rows upon Matrix drilldown

[Anonymous]

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

[v-juanli-msft]

Hi @Anonymous 

Firstly, let me confirm with you as follows:

What i get from your statement:

1. you have two tables: fact table, dimension table,

   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, 

   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".

If there is any wrong, please correct me.

In addition, please show some screenshots, or give your expected results.

Best Regards

Maggie

[Anonymous]

First thank, and apologies for the unusually large font size in my original message.... not sure how that happened

You have accurately summarized the premises.

Below are renderings of the matrix with and without drilldown.  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.   Here is the measure formula I've used, which features the HASONEVALUE expression to suppress a summed total in the price field.

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.  If I've erred in creating the formula, I'm hoping there is a simpler solution.

Many thanks

Bill

Rendering without Drilldown

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

Rendering WITH drilldown

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

[Greg_Deckler]

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