Forum Discussion
Filter cross different tables
I met the problem as below, like I have a table 1 information as below
Name receive Date Category
1 2017-10-10 A
2 2017-10-12 A
3 2017-10-11 B
4 2017-10-09 C
5 2017-10-10 C
I want to know the daily performance and create a calendar table 2 and calculate as below
Date Quantity
2017-10-09 1
2017-10-10 2
2017-10-11 1
2017-10-12 1
the data are huge and I just put a small parts of that, and what I want to get is I will filter the table 1 with the Category, like select the A, but I find it has no influence with the table 2, the data of table 2 never changed. I need to filter in the query, but that not I want.
So, on the report level,could I have any opportunity to achive that? when I slicer on the table 1 Category and the table 2 also chenged
Thank you for your great help
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(
9 Replies
Thank you, I tried taht, but I need to have a calendar and the X-axis should be the continious date like from the 1-Oct to 30-Oct, this is the reason I create a calendar table and count the quantity
So, if with measure, how could i do?
like quantity=countrows(table1) and then the receive date as X-axis?
