Forum Discussion
Dynamic table based on slicer
I have multiple tables, eg Sales table, Billing table, CN table, Receipt table where each of the table having a date field. Next, I have a date slicer. I want to allow user to select the date range and values selected from above tables display according to the date selected. how should i do?
I created a [Calendar Table] for the date slicer, however i cant link the [Calendar Table]date with all the date in tables above because it will resulted circular reference as Billing table, CN table, Receipt table all link back to Sales table.
I tried create a field with syntax below, however it doesnt work if there is no link define between [Calendar Table] and IFCA_CB_Receipt.
4 Replies
Hello siewwei ,
you shouldn't have the fact tables linked to each others. you need to have dimension tables that are common between all these fact linked to the fact.
thus the date table won't have a circular dependancy anymore.
check the concept of star schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
in Sales table i have CustomerID, then Sales table link to Billing, Collection and Credit Note table with Customer ID. in Billing it will have all the information about billing like billing date, document no and amount. in Collection it will have all information about collection like collection date, document no and amount. Same to Credit Note table. that's the reason i use Sales Table as the main table to link Sales, Collection and Credit Note.
Hello siewwei ,
then create a dim customer that has the customer info without duplication, and link this table to all other table through customer id. thus they will be all linked and you can link the date table to all of them as well.
Actually CustomerID (DebtorID) in Sales table is unique, no duplication. however, the value doesnt change according the date range selected. It gives me the total amount.
new Total Receipt =VAR _startdate = MIN('Calendar Table'[Date]) // Get the start date of the rangeVAR _enddate = MAX('Calendar Table'[Date]) // Get the end date of the rangeRETURNSUMX(FILTER(IFCA_CB_Receipt,IFCA_CB_Receipt[DebtorID] = IFCA_PS_Sale[DebtorID] &&NOT(ISBLANK(IFCA_CB_Receipt[DocNo])) &&IFCA_CB_Receipt[DocumentDate] >= _startdate && IFCA_CB_Receipt[DocumentDate] <= _enddate),IFCA_CB_Receipt[DocumentAmt])
