Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Redundant, mostly empty rows upon Matrix drilldown

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 pur...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    SymbolDescriptionQuantityPrice NowValue NowAmount InvestedTotal Gain/Loss
    04109KDC8ARKANSAS 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

     

    SymbolAccountPurchase DateDescriptionQuantityPrice NowValue NowAmount InvestedTotal Gain/Loss
    04109KDC8        
     AcctXYZ       
      1/13/2012  $ 101.70   
      10/16/2012  $ 101.70   
      10/18/2010  $ 101.70   
      11/21/2011ARKANSAS 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