Forum Discussion
Slicer isn't filtering data properly (with sample)
Hi!
I have been working on this tiny issue all day, and cannot solve it.
In the following sample dataset, there is 1 employees who submitted 2 messages from 2 different departments in the company.
The three tables are:
dim_org, a list of departments
| unit_id | unit_name |
| 500 | Consulting |
| 501 | HR |
dim_employees, a list of employees using SCD2
| employee_id | unit_id | start_date | end_date |
| 10 | 500 | 01/01/2023 | 01/02/2023 |
| 10 | 501 | 01/02/2023 | 01/03/2023 |
fact_messages, messages created by employees
| date_created | employee_id | message_key |
| 01/01/2023 | 10 | 1000 |
| 15/02/2023 | 10 | 1001 |
Relationships:
dim_org filters dim_employees on unit_id
dim_employees filters fact_messages on employee_id
The problem
When selecting a unit_name from a slicer, it does not filter the messages. Both messages are still shown.
I anticipated no problems, but I cannot make this work.
Here is the measure I tried:
MessageCount =
VAR SelectedUnitName = SELECTEDVALUE(dim_org[unit_name])
RETURN
CALCULATE(
COUNTROWS(fact_messages),
FILTER(
dim_employees,
dim_employees[start_date] <= MAX(dim_date[Date])
&& dim_employees[end_date] >= MIN(dim_date[Date])
),
FILTER(
dim_org,
dim_org[unit_name] = SelectedUnitName
)
)1 Reply
It does filter correctly but in your mesure you are not filtering the fact_messages based on the date, you are filtering the messages based on the relationships employee_id.
You probably want to add a filter on the date_created column of the fact_messages:
MessageCount = VAR SelectedUnitName = SELECTEDVALUE(dim_org[unit_name]) RETURN CALCULATE( COUNTROWS(fact_messages), FILTER( dim_employees, dim_employees[start_date] <= MAX(dim_date[Date]) && dim_employees[end_date] >= MIN(dim_date[Date]) ), FILTER( dim_org, dim_org[unit_name] = SelectedUnitName ), FILTER( fact_messages, fact_messages[date_created] <= SELECTEDVALUE(dim_employees[end_date]) && fact_messages[date_created] >= SELECTEDVALUE(dim_employees[start_date]) ), )Not sure if I have that syntax correct in the filter I have added but I think you get the idea ?
