Forum Discussion
Filter cross different tables
- Anonymous8 years ago
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(
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