Forum Discussion
Need help with filtering data between two tables
the current DAX expression only works for single month selections because it uses the SELECTEDVALUE function, which returns a blank when multiple values are selected in the slicer.
To handle multiple month selections, you can modify the DAX expression as follows:
Filtered TimeSaved =
VAR SelectedMonths = VALUES(Table1[DeploymentDate])
RETURN
CALCULATE(
SUM(Table2[TimeSaved]),
FILTER(
ALL(Table2),
Table2[Date] IN SelectedMonths && RELATED(Table1[DeploymentDate]) IN SelectedMonths
)
)Explanation of the modified DAX measure:
- Replace SELECTEDVALUE with VALUES to store all selected months in the SelectedMonths variable, even when multiple months are selected.
- Replace the = operator with IN to check if the Table2[Date] and RELATED(Table1[DeploymentDate]) are in the set of SelectedMonths.
Now the measure should work with multiple month selections in the DeploymentDate slicer. The "Filtered TimeSaved" measure will display the sum of TimeSaved for the processes deployed in the selected month(s).
Ghhousuddin Thank you so much for the reply.
I added a card visual. But when I select multiple months, the card displays blank and working only for a single month selection. Any help would be appreciated. Thanks.
the current DAX expression only works for single month selections because it uses the SELECTEDVALUE function, which returns a blank when multiple values are selected in the slicer.
To handle multiple month selections, you can modify the DAX expression as follows:
Filtered TimeSaved =
VAR SelectedMonths = VALUES(Table1[DeploymentDate])
RETURN
CALCULATE(
SUM(Table2[TimeSaved]),
FILTER(
ALL(Table2),
Table2[Date] IN SelectedMonths && RELATED(Table1[DeploymentDate]) IN SelectedMonths
)
)
Explanation of the modified DAX measure:
- Replace SELECTEDVALUE with VALUES to store all selected months in the SelectedMonths variable, even when multiple months are selected.
- Replace the = operator with IN to check if the Table2[Date] and RELATED(Table1[DeploymentDate]) are in the set of SelectedMonths.
Now the measure should work with multiple month selections in the DeploymentDate slicer. The "Filtered TimeSaved" measure will display the sum of TimeSaved for the processes deployed in the selected month(s).