Answered by:
two fact tables in one model  blank if measure does not "belong" to dimension

Hi,
I am working on a pretty complex data model that uses two facttables: 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 iscrossfilteredfunction 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
Question
Answers

well, that's "by design"
we need to show some value at the totalline, 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
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! :) Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Friday, October 25, 2013 7:50 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 viceversa
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 ISCROSSFILTEREDcheck 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 
 Proposed as answer by Ed Price  MSFTMicrosoft employee, Owner Friday, October 25, 2013 7:50 PM

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 dimensiontables).
=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.

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 dimensiontables).
=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! 

well, that's "by design"
we need to show some value at the totalline, 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