Need help with filtering data between two tables

[PowerAppUser]

Hi, I am trying to filter the data between two related tables but unable to achieve the desired output.
Let me walkthru with a sample data so it will be easy to understand the problem statement.

Table1: (Only one entry for ProcessName)

DeploymentDate	ProcessName	Department
Jan 2023	P1	Dept1
Jan 2023	P2	Dept2
Jan 2023	P3	Dept1
Feb 2023	P4	Dept2
Feb 2023	P5	Dept1

Table2: (Multiple entries for ProcessName - Month wise)

Date	ProcessName	TimeSaved
Jan 2023	P1	100
Jan 2023	P2	50
Jan 2023	P3	75
Feb 2023	P1	45
Feb 2023	P2	78
Feb 2023	P3	43
Feb 2023	P4	80
Feb 2023	P5	60

Relationships: Table1[ProcessName] -> Table2[ProcessName]

I have placed a slicer to apply the month filter with Table1 -> DeploymentDate

When filter applied, for example Jan 2023 is selected, TimeSaved should display the count as 225  instead displaying the count as 391 (which is adding both Jan 2023 and Feb 2023).

Kindly help me to fix this issue. The totals should be displayed only for the selected month(s) and the processes that are deployed in the selected month(s). Thanks in advance.

[PowerAppUser]

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.

[Ghhousuddin]

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.

[PowerAppUser]

@Ghhousuddin  Thank you so much. It worked.

[Ghhousuddin]

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:

1. Create a measure using the following DAX expression:
2. Filtered TimeSaved =
   VAR SelectedMonth = SELECTEDVALUE(Table1[DeploymentDate])
   RETURN
   CALCULATE(
   SUM(Table2[TimeSaved]),
   FILTER(
   Table2,
   Table2[Date] = SelectedMonth && RELATED(Table1[DeploymentDate]) = SelectedMonth
   )
   )
3. 1. 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:

   1. The SelectedMonth variable holds the value of the selected month in the DeploymentDate slicer.
   2. The CALCULATE function filters the sum of the TimeSaved column.
   3. 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.

[PowerAppUser]

@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.

[Ghhousuddin · Accepted solution]

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:

1. Replace SELECTEDVALUE with VALUES to store all selected months in the SelectedMonths variable, even when multiple months are selected.
2. 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).