none
Context in pivot table when row is foreign key RRS feed

  • Question

  • Can anyone explain the logic to me as to why the context for a pivot table row having a foreign key from a fact table on the row, includes the entire dimension table?

    My objective is to summarise the the fact table and to bring the description from the dim table, based on the join between the foreign and primary keys.

    

    I expected the pivot context to deliver a single value from the dim table because of the join.  Instead, I get every record in the dim table.  Can anyone please explain why? 

    Saturday, June 22, 2019 7:23 AM

Answers

  • The purpose of a relationship in power pivot is to propagate filters from dim to fact. That is what the arrow indicates - the direction of the filter propagation. The filters do not automatically propagate the other way. Filters come from visuals (pivot table rows, columns, slicers. Filters). When one or more of these locations are filtered, the filter propagates through the relationship to the fact table - that is what it is designed to do. In your case, you are filtering on the fact table. Fact table filters do not automatically filter the dim tables that is why you see 20 for each row. You could write a measure that filters the dim based on the fact. =CALCULATE(countrows(dim),fact)

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    • Marked as answer by Cool.Blue Sunday, June 23, 2019 3:56 AM
    • Unmarked as answer by Cool.Blue Sunday, June 23, 2019 4:16 AM
    • Marked as answer by Cool.Blue Sunday, June 23, 2019 5:11 AM
    Sunday, June 23, 2019 1:53 AM
    Answerer

All replies

  • The purpose of a relationship in power pivot is to propagate filters from dim to fact. That is what the arrow indicates - the direction of the filter propagation. The filters do not automatically propagate the other way. Filters come from visuals (pivot table rows, columns, slicers. Filters). When one or more of these locations are filtered, the filter propagates through the relationship to the fact table - that is what it is designed to do. In your case, you are filtering on the fact table. Fact table filters do not automatically filter the dim tables that is why you see 20 for each row. You could write a measure that filters the dim based on the fact. =CALCULATE(countrows(dim),fact)

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    • Marked as answer by Cool.Blue Sunday, June 23, 2019 3:56 AM
    • Unmarked as answer by Cool.Blue Sunday, June 23, 2019 4:16 AM
    • Marked as answer by Cool.Blue Sunday, June 23, 2019 5:11 AM
    Sunday, June 23, 2019 1:53 AM
    Answerer
  • Thanks Matt, that removes half of my confusion which helps a lot.

    Based on your explanation I can now see that the pivot table context is only determined by the Filter Context and it ignores the join.

    Although it doesn't change the core principle, I might have oversimplified a bit but.  My actual application is like this:

    

    I actually want to filter on the primary key in the state table and bring attributes in from the dim tables...


    I can understand from your answer why this does not return single values for Colour and Size.  The remaining confusion for me is why the joins are ignored in the pivot context.  It appears that autocomplete cross-joins all combinations and ignores the join in the Pivot, row context.

    On the other hand, when I use RELATED to create a calculated column, it takes the row context and the values "filtered" by the Join...

    So, the remaining confusion for me is that Joins are ignored in the Pivot context.  I think I "knew" this at some point but, I always forget because it is counter-intuitive for me.

    My work-around if I want to avoid calculated columns is something like this...

    If I don't use the FORMAT function by the way, it just fails silently because of mixed type return value.
    • Edited by Cool.Blue Sunday, June 23, 2019 6:11 AM
    Sunday, June 23, 2019 5:11 AM
  • There is no simple answer here.  The issue I think you are having is you are looking at this like SQL, but it is not SQL.  In a SQL world, you take tables and write a query (TSQL) to generate a new table containing (typically) a sub set of the original data.  You can do this to extract records and/or summarise data etc.  That is not how the Vertipaq engine works (Power Pivot).  Vertipaq has a semantic data model and a functional language (DAX).  You build the semantic model, use the User Interface to structure the table schema you want to see (ie a pivot table visual), and use functions to populate the table.  Now actually, DAX can also be used as a query language to produce a table just like you do in TSQL, but that is a different use case.  If you look deeper into DAX as a query language, you would be very comfortable applying your knowledge of TSQL.  Read my article here https://exceleratorbi.com.au/introduction-dax-query-language/

    Instead when using Power Pivot, you need to think differently.

    You will actually get exactly what you want if you do one simple thing. Take any column from your fact table and add it into the values section of the pivot table - then it will work the way you expect.

    On another level, you really should avoid the snowflake schema you are showing here.  Power Pivot is optimised for a star schema, so you would be better to denormalise colours and size into the State table.


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au


    Sunday, June 23, 2019 5:54 AM
    Answerer
  • OK, thanks Matt.
    Sunday, June 23, 2019 6:13 AM

  • You will actually get exactly what you want if you do one simple thing. Take any column from your fact table and add it into the values section of the pivot table - then it will work the way you expect.


    I get the rest of your answer but, not this bit.  My structure is normalised so there is nothing apart from a (non-unique) foreign key in the fact table that relates to the values I want to show.
    Sunday, June 23, 2019 6:24 AM
  • If you put a column, any column, from your fact table into the values section of the pivot table, you will get the results you referred to earlier.  Just do it, and you will see.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, June 23, 2019 7:05 AM
    Answerer
  • Mate, I did and I got this...


    Anyway, following your article I learned how to materialise a calculated table in excel.  This is awesome!

    For reference, I posted the workbook here.

    Sunday, June 23, 2019 8:30 AM
  • Can you please post your sample work book containing the snowflake schema 4 tables

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, June 23, 2019 8:43 AM
    Answerer
  • Please see the link in my previous answer mate.  Repeated here for your convenience.
    Sunday, June 23, 2019 9:00 AM
  • I didn't communicate what I had in mind clearly - sorry about that.  When you have items from dim tables on rows in a pivot, they don't get filtered.  If you add something from the fact table to the values, then the tables get filtered.  See example below

    here is the illustration

    here is the file


    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, June 23, 2019 9:17 AM
    Answerer
  • Wow!  That's interesting.  Thanks.

    I get the same behaviour with my tuned up measures that keep an eye out for null sets.

    I can get the compact form that I normally like.  To me this is the special sauce that keeps me interested in Pivot tables in excel...

    I also use ISFILTERED a lot to handle different values for different levels in the hierarchy.  So, I can have counts at the summary levels and descriptions on the leaves for example.  Or even different descriptions at different levels.  Have to use FORMAT though for the numbers or it fails silently and have to detect Total rows...

    Thanks for your patient explanation!

    Sunday, June 23, 2019 9:59 AM
  • No worries. I’m glad you have it doing what you want.

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, June 23, 2019 8:47 PM
    Answerer