Redundant Row upon Matrix Drill-down

[Anonymous]

In case I failed to meet recommended standards for posting, I am reposting my original question from a few days ago.... many thanks to Greg for forwarding recommended posting guidelines--

I  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 yields, 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.  

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. 

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

[#2]

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

[Anonymous]

Indeed this did the trick.... thank you very much 🙂

[MFelix · Accepted solution]

Hi @Anonymous ,

Withouth an example of you data model it's difficult to pin point your error however if you look at the formula what you are using is the following:

HASONEVALUE('Current Prices and Yields'[Price])  - Check if there is a single value for the Price column

VALUES('Current Prices and Yields'[Price])) - Returns the price column when there is a single value for the price formula

Otherwise blank

So looking in attention what you are returning is if the Price exist return a price otherwise return blank, what is happening is that probably in the way you have setup your data since there is a price related with all those dates and accounts you will get a value.

I assume you don't want nothing if there is no transactions correct?

I would do a differente calculation something similar to this:

Price Now =

IF(Sum(Table[Quantity])= BLANK(),BLANK(),VALUES('Current Prices and Yields'[Price]))

Try it and let me know if it works.