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).
Hi Ghhousuddin
The total sum was correctly updating in card.
But when comes to stacked column chart, the monthly values are not showing the data correctly.
For example: In Jan, TimeSaved (#process deployed : 3) = 100
In Feb, TimeSaved (#process deployed : 2) = 150.
If I select months Jan & Feb,
Time Saved = 100 (Jan for 3 processes) + 150 (Feb for 2 processes) + 200 (In Feb, it also contains the processes that were deployed in Jan).
The chart should display: Jan- 100 and Feb - 350
Instead displaying: Jan - 100 and Feb - 150 (Not considered the time saved for the previous selected month).
But total card displaying the sum correctly -> 450.
Any help would be appreciated. Thanks.
It sounds like the stacked column chart is not correctly aggregating the data across the selected months. To get the desired result, you need to ensure that the chart is summing the values for each month across all selected months, rather than just showing the values for the selected months individually.
Here's how you can modify your chart to display the correct data:
1. Open the stacked column chart in Power BI Desktop.
2. Click on the "Values" field in the "Visualizations" pane and select "Value field settings".
3. In the "Value field settings" dialog box, select "Sum" as the aggregation method for the "Time Saved" field.
4. Click OK to close the dialog box.
5. Click on the "Axis" field in the "Visualizations" pane and select "Month" as the category axis.
6. Select the "Data" view from the top of the report canvas to switch to the data view.
7. In the data view, select the "Time Saved" column and click on the "Modeling" tab in the ribbon.
8. Select "New measure" from the "Calculations" dropdown menu.
9. In the "New measure" dialog box, enter the following formula: `Time Saved (All) = CALCULATE(SUM(Table1[Time Saved]), ALL(Table1))`
10. Click OK to create the measure.
11. Go back to the stacked column chart and click on the "Values" field in the "Visualizations" pane.
12. Select "Time Saved (All)" from the list of available measures.
13. Preview the chart to see the correct data for the selected months.
This should ensure that the chart is correctly summing the data for each month across all selected months, rather than just showing the data for each selected month individually.