1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power BI Date Ranges

Discussion in 'Power Pivot, Power Map etc' started by msquared99, Nov 13, 2017.

  1. msquared99

    msquared99 Member

    Messages:
    163
    I have a table called SInvoices that has a column called invoiceDate. I am using a slicer to select a date range. I have another table called Commissions that has a column called startDate and another called endDate. I would like to have it when the slicer is used to select the date range it will populate the Commissions startDate and endDate columns with the date range that was selected.

    Is this possible?
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    Not without flattening table. You can't use 2 date columns on same table as related column. It causes error in relationship.

    One way to get around it, is to consolidate start date and end date into single column with another column serving as flag column (Start/End). This way, you only have single date column which can be related via date dimension table.
  3. msquared99

    msquared99 Member

    Messages:
    163
    Just thinking here... what if I created two columns in the table SInvoices called startDate and endDate then using an IF statement, based on the SInvoice dates, I could calculate the commission rate and then use the slicer?
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    Nope. Slicer must be based on single column and should not have looping/ambiguous relationship.

    I'd recommend either using flat structure or designing star schema with single fact table. See link for guide on how to build star schema.

    https://businessintelligist.com/201...n-power-bi-and-power-pivot-using-power-query/

    You could design star schema with multiple fact tables, but you should take care that there's no loop in relationship or cascading many to many relationships.

Share This Page