• 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...

  • 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

M

msquared99

Member
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?
 
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.
 
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?
 
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.
 
I figured out how to do this. I wrote SQL to do all the filtering, then simply pasted the SQL code in to Power BI. It works like a charm.
 
You must log in or register to reply here.
Back
Top