Forum Discussion
Filter cross different tables
A couple of ways you could do this then.
You could have a core date table where the dates are hinged to and then have inactive relationships between this Date table to each one of the columns in your fact table. Then you can make a measure for each of the dates using this method
receuptQty= CALCULATE(COUNTROWS(table1), USERELATIONSHIP(dates[The Date], table1[Receipt Date]))
uploadQty= CALCULATE(COUNTROWS(table1), USERELATIONSHIP(dates[The Date], table1[Upload Date]))
etc...
Then bring each one of these measures into your visual.
Depending on how you have your stuff setup this might be a sensible route, if not, you could unpivot the dates so you make a table looking like this
pivotedTable
DATE DATE TYPE CATEGORY VALUE
10/10 ReceiptDate A 3
10/10 UploadDate B 4
11/10 ReceiptDate A 5
11/10 UploadDate A 7
11/10 UploadDate B 5
Then make measures like this
CALCULATE(SUM(pivotedTable[Value]), FILTER('pivotedTable, pivotedTable[Date Type] = "ReceiptDate"))
CALCULATE(SUM(
To clarify then...
Is "plan receive quantity", "receipt quantity", "upload quantity", "Approve quantity" all categories in your original example?
And what is the x axis, as that is not a Date (unless that is hooked up to a Date table and the axis is showing weeks.
Or have I mis-understood entirely?
"receipt quantity", "upload quantity", "Approve quantity" are not in my original example, I just have the receipt date, upload date and approve date for each item,
I will count the quantity for each of them and make the chart as the "receipt quantity", "upload quantity", "Approve quantity" for different calendar date
the x axis is date( day of the date, like 1 stands for 1st-Oct)
so it's a little complicate, and thanks for your patience, hope I have clearify it for you and do you have any good idea for these
A couple of ways you could do this then.
You could have a core date table where the dates are hinged to and then have inactive relationships between this Date table to each one of the columns in your fact table. Then you can make a measure for each of the dates using this method
receuptQty= CALCULATE(COUNTROWS(table1), USERELATIONSHIP(dates[The Date], table1[Receipt Date]))
uploadQty= CALCULATE(COUNTROWS(table1), USERELATIONSHIP(dates[The Date], table1[Upload Date]))
etc...
Then bring each one of these measures into your visual.
Depending on how you have your stuff setup this might be a sensible route, if not, you could unpivot the dates so you make a table looking like this
pivotedTable
DATE DATE TYPE CATEGORY VALUE
10/10 ReceiptDate A 3
10/10 UploadDate B 4
11/10 ReceiptDate A 5
11/10 UploadDate A 7
11/10 UploadDate B 5
Then make measures like this
CALCULATE(SUM(pivotedTable[Value]), FILTER('pivotedTable, pivotedTable[Date Type] = "ReceiptDate"))
CALCULATE(SUM(
Hi tmckenzie,
Thanks a lot, you have provide so many ideas and both are works, I will need look into it and develop my dashboards