Forum Discussion
Set different behavior for the same slicer filter
I have informations from two different tables combined on the same visual table using measures.
I'm using a slicer to filter informations from both original tables based on a date column.
What I did to get the filter working was to create a third table with a date column and create a relationship between this date column with date column from the other two tables.
The filter is working well, filtering start and end date from both tables.
My problem is that for the first table I need to filter just the end date, so I get cumulative value from column "Information". And for the second table I need to filter all the period (start and end date).
Example:
Table 1: Need to filter just the end date
| Date | Information | Category |
| 11/10/2022 | 1 | a |
| 11/12/2022 | 2 | a |
| 11/14/2022 | 3 | a |
| 11/15/2022 | 4 | a |
Table 2: Need to filter both start and end date
| Date | Information | Category |
| 11/12/2022 | 1 | a |
| 11/14/2022 | 2 | a |
| 11/15/2022 | 3 | a |
| 11/16/2022 | 4 | a |
Supposing I have a count and a sum measure for Information column, on the visual table I should get the below, if I set this period on the slicer filter: 11/14/2022 to 11/15/2022
| Category | Count of Information | Sum of Information |
| a | 5 | 11 |
Basically, for Table 1 the period should be 11/10/2022 to 11/15/2022 and for Table 2 should be 11/14/2022 to 11/15/2022.
Is that possible using the same slicer filter?
Thank you!
Anonymous You could use a similar method to what I did here: https://excelwithallison.blogspot.com/2021/08/categorical-date-slicer-in-power-bi.html by creating a measure that you can add as a visual filter to the table. You'll need to make sure your date in the slicer is NOT related to the data in the table for that method to work.
2 Replies
Anonymous You could use a similar method to what I did here: https://excelwithallison.blogspot.com/2021/08/categorical-date-slicer-in-power-bi.html by creating a measure that you can add as a visual filter to the table. You'll need to make sure your date in the slicer is NOT related to the data in the table for that method to work.
Thank you so much AllisonKennedy !!
I didn't think about using filtered data from slicer on the measures.
Basically I've created my measures (sum and count) without date filter for the Table 2 (so it was filtered directly by slicer filter) and with filter for Table 1. Then created a relationship between date column of Table 2 table and date column of my Date Table. Also I've deleted Table 1 date column relationship with Date Table so it was filtered by the measure.
For those who need a hint:
Step 1:
CALCULATE(
SUM('Table1'[Information]) + SUM('Table2'[Information]), FILTER('Table2', 'Table2'[Date Column] <= MAX(['Date Table'[Date Column]]))Step 2: Create a relation between "Table 1" date column and "Date Table" date column.Step 3: Delete any relation between "Table 2" and "Date Table", otherwise your filter won't work.