Can you write measures across mutliple tables in PowerPivot?

[PatrickWW]

I have an exception report which essentially takes true false (1,0) Monday - Sunday and compares off each other. If they match true if not false. Pretty simple IF statement. I'm curious if you import two tables that are normalized can the measure be written for data in both tables. Example comparing Monday L verse Monday G. I created calculated columns in both tables turning the fields into bits 1,0 Thanks for any explanation. I am reading through the DAX book but nothing indicates that you can.

 

[paivers]

Sure you can, so long as you can get context to the rows via the relationship, you can either use in calculated fields or measures by simply referring to the table[column] you want. - Pete

 

[PatrickWW]

Good to know. All the examples I have seen always refer back to the same table. Kind of pointless if you asked me, one of the big value adds to PowerPivot is cross table formulas. Thanks again!

 

[paivers]

Typically though you want to have one main fact table that pulls in copies of the related data for quick calculations and making it easy for your end users to find it. You are basically assembling a flat model. Its the typical star schema and dimensions, which is why a lot of books look at it that way. Sometimes however you will just want a measure based on aggregates from another table and you can do that.

 

[PatrickWW]

I talked with a few of our DBA's and it sounds like you need some decent knowledge to set up an infrastructure to do that. I am a business analyst, so dimension tables etc are not my forte. I just started building temp tables a few months ago lol.

 

[paivers]

I know where your DBA's are coming from, a full star schema can be daunting, but you can still don something basic. Lets suppose the main "facts" you want to report on are accounts. That is the table you want to report from and use other tables to enrich that. You might have an office key on the account, and another table of offices including name, and location. You would relate the two with the key (fact table on top), then copy the fields of interest to the facts table by creating a calculated field such as fact[Location]=related(Office[Location]). Seems like duplication and it is, but it gives a quick and easy slicer on your accounts, so you can pick out all accounts for an office without knowing how the data is related. Most importantly your sum measures will respect this location slicer.

 

[astrodon]

What if, for whatever reason, there was a need to copy a fact table to be saved as a new table in the data model? Would/could all of the measures in the copied table be brought to the new table?

TIA
doco