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).
To achieve the desired output, you can create a new measure in Power BI that filters the TimeSaved column based on the DeploymentDate slicer selection. To do this, follow these steps:
- Create a measure using the following DAX expression:
- Filtered TimeSaved =
VAR SelectedMonth = SELECTEDVALUE(Table1[DeploymentDate])
RETURN
CALCULATE(
SUM(Table2[TimeSaved]),
FILTER(
Table2,
Table2[Date] = SelectedMonth && RELATED(Table1[DeploymentDate]) = SelectedMonth
)
) - Add a card visual to your report and use the newly created measure "Filtered TimeSaved" as the field.
Now, when you select a month using the DeploymentDate slicer, the "Filtered TimeSaved" measure will display the sum of TimeSaved for the processes deployed in the selected month(s).
Explanation of the DAX measure:
- The SelectedMonth variable holds the value of the selected month in the DeploymentDate slicer.
- The CALCULATE function filters the sum of the TimeSaved column.
- The FILTER function filters the Table2 rows based on the condition that the Date column matches the selected month, and the related DeploymentDate from Table1 also matches the selected month.
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).