This topic contains 4 replies, has 2 voices, and was last updated by 
-
Posts
-
I am in desperate need of guidance. I have two fact tables and five look up tables. Fact Tables: Sales Performance and Campaign Results. Look Up tables: Date Table, SuperCat, Level2, Endleaf, and manufacturer. I created a merged fact table of the campaign results and assigned categorizes and connected it to the lookup tables along with the sales performance table leaving the original fact table disconnected. Unfortunately, I wrote the measures to the disconnected, original table. I know this is not normally a problem as I could simply connect the relationships but the problem is that they all worked. I had no idea I wrote them to the wrong table until I was doing some data model clean up. If I connect the Campaign Results table to the look up tables the measures break.
First, How? How do they work if they are not connected?
Second, Why? Why do they break if I connect them?
Third, Should? Should I leave them disconnected?
Fourth, Trust? Can I trust the results?
Added complication: If I rewrite the measures as new measures to the connected, merged table they do not work.
Responses to questions:
That they “work” does not mean they function as per specification.
The reason why they break should be given in error and warning messages within the appropriate sheet tab of the Power Pivot window. You may have to click on error or warning icons to get the text of their message.
You should not need to re-type the measures to move them to a new table you could select a different table name in the drop down of the Measure window.
If you do not understand what is happening in the power pivot window, I would not trust the results.
—
Also curious why you merged (joined) your two data tables because, to me, they seem like different sets of facts. If they both share the same lookup tables, and fields from shared lookup tables (not fields from the data tables) are placed in the rows, columns or filters drop zones of a pivot field list, you should be able to use measures from the two data tables in the same pivot.
Are you able to put together some sample anonymized data into a workbook and upload?
Tom
Clarification: They do not break, they go blank. And, the merged table is from a lookup set and one of the fact tables. The merge was unnecessary and I would prefer to use the original fact table. However, when I connect the relationships, the numbers disappear. I am assuming, at this point, that my calculation has an error because when I rewrite, it is still blank.
Here is the measure:
CALCULATE([Total Campaign Results],
FILTER(‘Merged campaign information’,
‘Merged campaign information'[Vendor]=”Vendor”||
‘Merged campaign information'[Vendor]=””||
‘Merged campaign information'[Vendor]=”Keyword”||
‘Merged campaign information'[Vendor]=”Branding”||
‘Merged campaign information'[Vendor]=”–“||
‘Merged campaign information'[Vendor]=”Competitor”||
‘Merged campaign information'[Vendor]=”Google Shopping”)
) *
[% Cumulative Total]
Pulling out the card to step through it now.
You could make a copy of the current measure and break into two other measures: one for the calculate and one for the % Cumulative total and see if one or both components are returning blank.
Solved it with the card. While stepping through the measure I realized that this part: [% Cumulative Total] was referencing a different category. Therefore, I was multiplying by zero which Siri will tell you is futile. I did discover it by using your method of adding the measure back to the visible data. Thank you for your help and emotional support.
The forum ‘Power Pivot’ is closed to new topics and replies.