SOLVED
Home

Multiple Data Models in Excel / Power Pivot

%3CLINGO-SUB%20id%3D%22lingo-sub-790210%22%20slang%3D%22en-US%22%3EMultiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790210%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20possible%20to%20have%20two%20data%20models%20in%20Excel%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20limit%20the%20data%20in%20a%20file%20that%20is%20mass%20customized%20for%2032%20sales%20people.%20The%20file%20needs%20two%20queries%20in%20order%20to%20be%20able%20to%20calculate%20their%20commissions%20correctly.%20One%20query%20looks%20for%20sales%20the%20sales%20person%20is%20classified%20as%20the%20outside%20rep.%20The%20other%20looks%20for%20them%20as%20the%20inside%20rep.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20way%20is%20to%20write%20a%20SQL%20that%20pulls%20in%20data%20where%20they%20are%20either%20the%20outside%20or%20inside%20rep.%20I%20can't%20figure%20that%20out.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-790210%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EData%20Model%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790219%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790219%22%20slang%3D%22en-US%22%3EThere%20should%20be%20a%20table%20containing%20a%20field%20which%20states%20whether%20a%20sale%20is%20out-%20or%20in-side.%20You%20can%20use%20that%20field%20to%20filter%20the%20report%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790226%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20but%20that%20brings%20in%20all%20sales%20reps%20into%20the%20data%20model%20and%20we%20need%20to%20limit%20the%20data%20to%20the%20specific%20rep%3A%20too%20many%20rows%20and%20we%20don't%20want%20them%20seeing%20other%20reps%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790243%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790243%22%20slang%3D%22en-US%22%3EI%20don't%20see%20why%20that%20would%20be%20a%20problem%20with%20the%20solution%20I%20propose.%20Either%20way%20you%20would%20need%20to%20filter%20the%20data%20for%20the%20rep%20opening%20the%20file%2C%20wouldn't%20you%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790251%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790251%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20tow%20fields%3A%20Inside%20Rep%2C%20Outside%20rep.%20I%20need%20all%20data%20where%20the%20report%20rep%20is%20either%20of%20those.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-790399%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-790399%22%20slang%3D%22en-US%22%3ESo%20the%20query%20would%20need%20a%20filter%20similar%20to%3A%3CBR%20%2F%3EWHERE%20%5BInside%20Rep%5D%3D%22Name%20of%20rep%22%20Or%20%5BOutside%20rep%5D%3D%22Name%20of%20rep%22%3CBR%20%2F%3EAnd%20then%20you%20could%20just%20filter%20your%20pivottable%20for%20blanks%20on%20either%20the%20Inside%20rep%20or%20the%20outside%20rep%20field.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793307%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793307%22%20slang%3D%22en-US%22%3E%3CP%3EI%20figured%20out%20the%20SQL.%20Interesting%20thing%20is%20that%20you%20can't%20place%20in%20the%20Where%20clause%20items%20that%20are%20in%20the%20Select%20clause.%20That%20was%20the%20issue.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-793327%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20Data%20Models%20in%20Excel%20%2F%20Power%20Pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-793327%22%20slang%3D%22en-US%22%3EIf%20I'm%20not%20sure%20about%20the%20syntax%20of%20a%20query%20I%20tend%20to%20use%20MSAccess'%20querydesigner%20to%20build%20the%20query.%3C%2FLINGO-BODY%3E
Highlighted
Moscuba
New Contributor

It is possible to have two data models in Excel? 

 

I need to limit the data in a file that is mass customized for 32 sales people. The file needs two queries in order to be able to calculate their commissions correctly. One query looks for sales the sales person is classified as the outside rep. The other looks for them as the inside rep. 

 

Another way is to write a SQL that pulls in data where they are either the outside or inside rep. I can't figure that out. 

 

Thank you. 

7 Replies
There should be a table containing a field which states whether a sale is out- or in-side. You can use that field to filter the report
Highlighted

@Jan Karel Pieterse 

 

Thank you but that brings in all sales reps into the data model and we need to limit the data to the specific rep: too many rows and we don't want them seeing other reps data.

Highlighted
I don't see why that would be a problem with the solution I propose. Either way you would need to filter the data for the rep opening the file, wouldn't you?
Highlighted

@Jan Karel Pieterse 

 

Hi,

 

There are tow fields: Inside Rep, Outside rep. I need all data where the report rep is either of those.

Highlighted
So the query would need a filter similar to:
WHERE [Inside Rep]="Name of rep" Or [Outside rep]="Name of rep"
And then you could just filter your pivottable for blanks on either the Inside rep or the outside rep field.
Highlighted
Solution

I figured out the SQL. Interesting thing is that you can't place in the Where clause items that are in the Select clause. That was the issue. 

Highlighted
If I'm not sure about the syntax of a query I tend to use MSAccess' querydesigner to build the query.
Related Conversations
IF statements and conditional formatting
clare1981 in Excel on
1 Replies
Reverse numbers and characters in worksheet
David_Gerrior in Excel on
1 Replies
Sumifs
Jsbluemoon82 in Excel on
3 Replies
Excel formula similiar to texjoin
Carlo74 in Excel on
0 Replies
everything is black and white in excel
Gold4trees in Excel on
1 Replies
Deleting unwanted rows and columns
chipg900 in Excel on
4 Replies