none
two fact tables in one model - blank if measure does not "belong" to dimension

    Question

  • Hi,

    I am working on a pretty complex data model that uses two fact-tables: sales and purchase transactions.

    Some dimensions such as product are shared, some others (e.g. customer, vendor) of course not. Measures in Powerpivot that are shown for Dimension entries that have no relationship to the fact table (e.g. let's say that I have vendor Name as lines in my Pivot table and I am showing the measure Revenue sales), the Pivot table is just repeating the entire (unfiltered) amount:

    This makes our data model very difficult to use; our users often jump between dimensions of both areas.

    I tried to leverage the iscrossfiltered-function to check if my sales (or purchase) table has any filter set:

    =IF(NOT(ISCROSSFILTERED('Fact Sales'[Doc No]));BLANK();Sum('Fact Sales'[Sales Amount]))

    That is working good - with one exception: the total line would be shown as blank since the total line is not filtered according to iscrossfiltered. I thought about using HasOneValue to find out if I am on a total line. The problem with this aproach is that we have a high number of dimension attributes and our measures would have to check each one what would make the measures very long unreadable.

    Has anyone an idea what to do about it?

    Best regards in advance

    Sunday, October 20, 2013 8:05 PM

Answers

  • well, that's "by design"

    we need to show some value at the total-line, otherwise the calculation would always show Blank() if that column is not used in your report

    hope that makes sense to you

    -gerhard


    - www.pmOne.com -

    • Marked as answer by Julian Wissel Wednesday, October 30, 2013 6:32 PM
    Tuesday, October 29, 2013 9:51 AM

All replies

  • Can you try the reverse approach? As in, do something like if it is crossfiltered by the dimensions that have no relation, show as blank.

    =IF(ISCROSSFILTERED('Vendor'[Vendor Names]);BLANK();Sum('Fact Sales'[Sales Amount]))

    This way, you will not have any value for the lines, but the total will show values.


    Cheers,
    Jason | www.SqlJason.com
    P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

    Some Random Thoughts

    Follow me on Twitter

    Wednesday, October 23, 2013 7:51 PM
  • to show only values of associated records (=same shared dimension values) you may also use this calculations:

    SumSales:=CALCULATE(SUM('Fact Sales'[Sales Amount]), 'Fact Purchases')
    and vice-versa
    SumPurchases:=CALCULATE(SUM('Fact Purchases'[Purchase Amount]), 'Fact Sales')

    this may look a bit confusing, but just try it :)

    to make your current approach also work on the total line you would have to add an ISCROSSFILTERED-check also on the other table

    =IF(NOT(ISCROSSFILTERED('Fact Sales'[Doc No])) && ISCROSSFILTERED('Fact Purchases'[other column]);BLANK();Sum('Fact Sales'[Sales Amount]))

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, October 24, 2013 10:18 AM
  • Thank you Jason and Gerhard for your help!

    I think

    =IF(ISCROSSFILTERED('Vendor'[Vendor Names]);BLANK();Sum('Fact Sales'[Sales Amount]))

    does not work in my case since I have some shared dimensions and I try to use the ISCROSSFILTERED on my fact table (otherwise I would have to reference too many dimension-tables).

    =IF(NOT(ISCROSSFILTERED('Fact Sales'[Doc No])) && ISCROSSFILTERED('Fact Purchases'[other column]);BLANK();Sum('Fact Sales'[Sales Amount]))

    seems to be very close to what I need. I just see one problem: it always shows the total line even in cases where all dimension members resulted in "blank". That makes sense since the second part of the AND would return false.

    Thursday, October 24, 2013 11:43 AM
  • Thank you Jason and Gerhard for your help!

    I think

    =IF(ISCROSSFILTERED('Vendor'[Vendor Names]);BLANK();Sum('Fact Sales'[Sales Amount]))

    does not work in my case since I have some shared dimensions and I try to use the ISCROSSFILTERED on my fact table (otherwise I would have to reference too many dimension-tables).

    =IF(NOT(ISCROSSFILTERED('Fact Sales'[Doc No])) && ISCROSSFILTERED('Fact Purchases'[other column]);BLANK();Sum('Fact Sales'[Sales Amount]))

    seems to be very close to what I need. I just see one problem: it always shows the total line even in cases where all dimension members resulted in "blank". That makes sense since the second part of the AND would return false.

    Any further questions?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, October 25, 2013 7:50 PM
  • Sorry, I forgot to write down an explicit question:

    Does anyone know how I can solve my remaining problem?

    Monday, October 28, 2013 10:54 AM
  • well, that's "by design"

    we need to show some value at the total-line, otherwise the calculation would always show Blank() if that column is not used in your report

    hope that makes sense to you

    -gerhard


    - www.pmOne.com -

    • Marked as answer by Julian Wissel Wednesday, October 30, 2013 6:32 PM
    Tuesday, October 29, 2013 9:51 AM