Forum Discussion
Filter on dimensional Data based on Date Key
Hi community!
I have a dimensional table that I would like to filter based on a date filter.
The structure is like this:
| Article | Department | Value |
| A | Packaging | 5 |
| B | Polishing | 6 |
| C | Shipping | 5 |
| A1 | Assembly | 5 |
Now, I would like to exclude / filter out based on the following conditions:
- If Date > 01/01/2023 then
- Remove Packaging and Shipping
- If Date <= 01/01/2023 then
- Remove Polishing
- Keep Assembly allways, regardles of Date Filter.
How would you do that?
Hi joshua1990 not tested with number. Still, try measure below
CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]>DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Packaging","Shipping" )
)
+
CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]<=DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Polishing" )
)
+CALCULATE( SUM(Tabl[Value]),
FILTER(Table, Table[Depatment] IN {"Assembly " )
)You can try this solution as well.
FOr this you need to have 2 DAX. One as a measure and another as a calculated column.
Below are the codes.
Measure:
Current Date Filter =
MAX('Date'[Date])
Calculated Column:Filtered =
VAR CurrentDate = [Current Date Filter]
VAR IsPackagingOrShipping =
'DimensionalTable'[Department] IN {"Packaging", "Shipping"}
VAR IsPolishing =
'DimensionalTable'[Department] = "Polishing"
VAR IsAssembly =
'DimensionalTable'[Department] = "Assembly"RETURN
IF(
IsAssembly,
1, -- Always include Assembly
IF(
CurrentDate > DATE(2023, 1, 1),
IF(
IsPackagingOrShipping,
0, -- Exclude Packaging and Shipping
1 -- Include other departments
),
IF(
IsPolishing,
0, -- Exclude Polishing
1 -- Include other departments
)
)
)
In your visual you can Drag the "Filtered" column to the Filters pane of your visual and set it to only include rows where "Filtered" is 1.
Try this and see if the code works.
2 Replies
Hi joshua1990 not tested with number. Still, try measure below
CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]>DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Packaging","Shipping" )
)
+
CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]<=DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Polishing" )
)
+CALCULATE( SUM(Tabl[Value]),
FILTER(Table, Table[Depatment] IN {"Assembly " )
)You can try this solution as well.
FOr this you need to have 2 DAX. One as a measure and another as a calculated column.
Below are the codes.
Measure:
Current Date Filter =
MAX('Date'[Date])
Calculated Column:Filtered =
VAR CurrentDate = [Current Date Filter]
VAR IsPackagingOrShipping =
'DimensionalTable'[Department] IN {"Packaging", "Shipping"}
VAR IsPolishing =
'DimensionalTable'[Department] = "Polishing"
VAR IsAssembly =
'DimensionalTable'[Department] = "Assembly"RETURN
IF(
IsAssembly,
1, -- Always include Assembly
IF(
CurrentDate > DATE(2023, 1, 1),
IF(
IsPackagingOrShipping,
0, -- Exclude Packaging and Shipping
1 -- Include other departments
),
IF(
IsPolishing,
0, -- Exclude Polishing
1 -- Include other departments
)
)
)
In your visual you can Drag the "Filtered" column to the Filters pane of your visual and set it to only include rows where "Filtered" is 1.
Try this and see if the code works.