Forum Discussion
Filter a table based on a slicer
Hello Everybody !
I'm new to power BI and so i'm trying to filter a table based on a slicer
I have a [Date added] for the data and i want to select only rows where the date difference between [date added] and today
Here's the dax query that i came up with so far :
Filter= IF(VALUE(DATEDIFF('Extraction devis organisation'[Date added].[Date];TODAY();DAY)) >= MIN(Slicer[SLICER Selection]) ; "O";"N")
and on the filter panel i select only rows with "O"
The slicer table has no relationships with other tables and yet it doesn't work
Does anyone have a clue why
Thany you
Let's break down the issues in your DAX formula and fix them.
DATEDIFF Function: The usage of DATEDIFF is correct but you might want to simplify the reference to the date column without .[Date] if it's not a hierarchy.
Using MIN with Slicer: If your slicer is single-select, then the use of MIN might be unnecessary, because the slicer can have only one value selected at a time. However, if you want it to be multi-select and take the minimum value, then this is correct.
Filtering in visuals: Instead of creating a column that outputs "O" or "N" and then filtering by this column, you can create a measure that returns TRUE or FALSE, and use this measure as a visual-level filter.
Relationships: DAX relies heavily on relationships. However, for slicers, if you are directly referencing the value in a measure or calculated column like you are, a relationship isn't always necessary.
Let's fix your formula by creating a measure that returns TRUE or FALSE:
FilterMeasure =
VAR DaysDifference = DATEDIFF('Extraction devis organisation'[Date added], TODAY(), DAY)
VAR SlicerValue = MIN(Slicer[SLICER Selection])
RETURN
DaysDifference >= SlicerValue
You can then use FilterMeasure as a visual-level filter and set it to TRUE.However, if you want to stick with the column approach:
FilterColumn =
VAR DaysDifference = DATEDIFF('Extraction devis organisation'[Date added], TODAY(), DAY)
VAR SlicerValue = MIN(Slicer[SLICER Selection])
RETURN
IF(DaysDifference >= SlicerValue, "O", "N")
And continue filtering with "O" in your visual.In both methods, it's essential to ensure:
Date added column has proper date values without any blanks or invalid entries.
Your slicer is functioning correctly, meaning it has valid numeric values for the selection.
1 Reply
- technolog
Super User
Let's break down the issues in your DAX formula and fix them.
DATEDIFF Function: The usage of DATEDIFF is correct but you might want to simplify the reference to the date column without .[Date] if it's not a hierarchy.
Using MIN with Slicer: If your slicer is single-select, then the use of MIN might be unnecessary, because the slicer can have only one value selected at a time. However, if you want it to be multi-select and take the minimum value, then this is correct.
Filtering in visuals: Instead of creating a column that outputs "O" or "N" and then filtering by this column, you can create a measure that returns TRUE or FALSE, and use this measure as a visual-level filter.
Relationships: DAX relies heavily on relationships. However, for slicers, if you are directly referencing the value in a measure or calculated column like you are, a relationship isn't always necessary.
Let's fix your formula by creating a measure that returns TRUE or FALSE:
FilterMeasure =
VAR DaysDifference = DATEDIFF('Extraction devis organisation'[Date added], TODAY(), DAY)
VAR SlicerValue = MIN(Slicer[SLICER Selection])
RETURN
DaysDifference >= SlicerValue
You can then use FilterMeasure as a visual-level filter and set it to TRUE.However, if you want to stick with the column approach:
FilterColumn =
VAR DaysDifference = DATEDIFF('Extraction devis organisation'[Date added], TODAY(), DAY)
VAR SlicerValue = MIN(Slicer[SLICER Selection])
RETURN
IF(DaysDifference >= SlicerValue, "O", "N")
And continue filtering with "O" in your visual.In both methods, it's essential to ensure:
Date added column has proper date values without any blanks or invalid entries.
Your slicer is functioning correctly, meaning it has valid numeric values for the selection.