Filter cross different tables

[Henry1943]

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

[Anonymous]

If you make a measure for countrows(table1) then you shouldn't need table 2 at all. You should be able to do it with just that.

[Henry1943]

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?

[Anonymous]

As long as date is formatted as a date and not a string it should be able to become a continuous value on the X axis. And yes, how you have written the measure formula looks perfect.

[Henry1943]

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.

[Anonymous]

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?

[Henry1943]

"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

[Anonymous · Accepted solution]

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(

[Henry1943]

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

[Henry1943]

"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