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(
Thank you for your prompt reply and that really helps a lot, but I am still a little confusion, as I mentioned, those data are just parts of it and I have the data 2, date 3 colunm, and what I want is the table as below
and even the mesure is a good idea but I can't get chart like it, and I am wondering if we have other masure help me to achive that or could the filter cross tables.
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
"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(