Home Forums Power Pivot lookup tbl from data tbl? + 2 calendar tbls?

This topic contains 7 replies, has 4 voices, and was last updated by  tomallan 1 month, 2 weeks ago.

Viewing 8 posts - 1 through 8 (of 8 total)
  • Author
    Posts
  • #4233

    chefe
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Hi there

    First of all I have to get something off my chest. Thank you for authoring your book! I’ve just finished the 2nd ed of PP/PBI (in record time for circumstances 😉 and I’m glad to say this was a very (!) good read and in most places a real eye opener.

    Having said this, I want to refer my first question to chapter 20. <span style=”line-height: 1.5;”>In this chapter – among other this things – you make case for using Power Query to make lookup tables from your data table(s). On page 197 you do so by starting “from scratch”, i.e. selecting the “from database” dialogue and then choosing “from access database”, afterwards deleting the non-needed columns.</span>

    Has this a performance advantage compared to just right-clicking the already existing data table in the Power Query editor and choose “reference”?

    And for my second question. Assume I have two relevant date columns in my data table (e.g. transaction start date, transaction end date). If I want to only have one calendar table, I can apply two relationships to my data table (one active, the other inactive), and choose the relevant relationship in my measures using USERELATIONSHIP().

    If however, I want to use fields (on rows, columns…) from my the calendar table in the same pivot for both transaction start date AND transaction end date, I need actually TWO separate calendar tables.

    Is this correct? Or is some kind of wizardry to work around this? Since I would have a redundant lookup table.

    If I was not clear somewhere, sorry, I’m still a newb at Power Pivot.

     

    #4237

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Hi chefe,

    A “gotcha” can be experienced choosing the reference option if one does not consider that the referencing query will start with what the referenced query has ended with.

    Regarding the idea of one or two calendar tables, I think one calendar table should be sufficient. A couple days ago I created a pivot where the fact table had two columns that referenced the date column in one calendar table. One of the relationships was active, the other inactive. Measure amounts based on different relationships found their way to the appropriate date. Note: To those following this post not familiar with USERELATIONSHIP, the active relationship does not require the use of USERELATIONSHIP, only the inactive relationship(s).

    Tom

    #4255

    chefe
    Participant
    • Started: 1
    • Replies: 1
    • Total: 2

    Thank you!

    But if I want to use the two date related fields at the same time on the pivot on rows and columns and not in measures, do I have another option than to use two calendar tables?

    Cheers

     

    #4256

    matthew
    Participant
    • Started: 0
    • Replies: 9
    • Total: 9

    You can only put a column of values from a lookup table in one of: row, column, filter area. If you want to use in two, having 2 calendar tables is probably the easiest way. If you go with 2 keep in mind that even though each calendar table is related to a different date field in the fact table, both filters will be in the evaluation context and filter the results. You’ll need to ALL() one of them in the other’s measure. StartMeasure:= Calculate( sum ( FactTable[OrderAmount] ), All(EndCalendar) ). EndMeasure := Calculate( sum( FactTable[OrderAmount]), All (StartCalendar) ).

    EDIT: this assumes you mean that you want to put the same column ( Calendar[Date] ) in rows & columns. You can put different columns from date table. Say ‘Year’ in columns, and ‘Months’ on rows.

    #9920

    damiruka
    Participant
    • Started: 2
    • Replies: 17
    • Total: 19

    Hi mr. Allan

    Please help. I have exchange rates , not all dates, but I have to drill down on all dates to multiplay with sales amount.

    Best regards

     

     

    Attachments:
    You must be logged in to view attached files.
    #9929

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Damir,

    Please see attached workbook.

    Since the exchange rate table does not have all dates, it would be better to use a disconnected table for exchange rates instead of a related lookup table.

    In the attached workbook you will find formulas to be used in calculated columns when using your exchange rates table. One formula would be used for a disconnected exchange rate table and the other would be used for a related table. The only difference between the two is that one uses ALL (to override the filter created by the relationship).

    Attachments:
    You must be logged in to view attached files.
    #9936

    damiruka
    Participant
    • Started: 2
    • Replies: 17
    • Total: 19

    Mr. Allan

     

    it works, thanks a lot.

     

    Regard

    #9943

    tomallan
    Keymaster
    • Started: 9
    • Replies: 2308
    • Total: 2317

    Damir,

    You’re welcome!

    Tom Allan

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic.