Need asistance in how to show common values when selecting multiple values from Month slicer

[Vaibhav05]

Hi Team, I want to showcase the common values from month selection in slicer. E.g in below table when i select Jan'24 and Feb'24 months, the result should show only common values i.e A and B.
Month-Year is coming from date field and Friend is coming from different field.

Month	Friend
01 January 2024	A
01 January 2024	B
01 January 2024	C
01 February 2024	A
01 February 2024	B
01 February 2024	E
01 February 2024	F

[Anonymous]

Hi @Vaibhav05 ,

Below is my table1:

Below is my table2:

The following DAX(new table) might work for you:

Common = 
var A_t = CALCULATETABLE(VALUES('Table'[Friend]),FILTER('Table','Table'[Month] = "1/1/2024"))
var B_t = CALCULATETABLE(VALUES('Table'[Friend]),FILTER('Table','Table'[Month] = "2/1/2024"))
RETURN
 INTERSECT(A_t,B_t)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vaibhav05]

Thanks for your response.

Since i have data close to 4 years so i am trying to to create a measure where it can dynamically filter common Friends name based on the any multiple months selection.
I have tried providing month and year criteria but no luck.
Do i have to manually create variable for every month ?

[Anonymous]

Hi @Vaibhav05 ,

Maybe yes, check each user name into the calculation table to show different results.It's a tough way to go, though.

I'll think of something better.

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[123abc]

To showcase common values when selecting multiple months from the slicer, you can use a measure in Power BI that calculates the intersection of the selected months and displays only the friends that appear in all selected months. Here's how you can do it:

1. Create a New Measure: Go to your Power BI model and create a new measure. You can name it something like "Common Friends".

2. Use DAX to Calculate Common Friends: Use the following DAX formula for your measure:

Common Friends =
VAR SelectedMonths =
CALCULATETABLE (
VALUES ( 'YourDateTable'[Friend] ),
ALLSELECTED ( 'YourDateTable'[Month-Year] )
)
RETURN
INTERSECT (
SelectedMonths,
VALUES ( 'YourDateTable'[Friend] )
)

Replace 'YourDateTable' with the name of your date table and 'Month-Year' with the name of your Month-Year column.

This measure works by first calculating the set of friends that are selected based on the months chosen in the slicer using ALLSELECTED. Then, it calculates the intersection of the selected friends across all selected months using the INTERSECT function.

1. Display Common Friends: Now, you can drag the "Common Friends" measure into your visualizations to display the common friends for the selected months.

When you select multiple months in your slicer, the visualization will dynamically update to show only the friends that are common across all selected months.

This approach should help you achieve your requirement of showing common values when selecting multiple months from the slicer in Power BI.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Vaibhav05]

Hi, Thanks for the solution I have created the measure but not able to view that to the visual. Below is the error i am getting: Can you please suggest what i am doing wrong here ..

[Vaibhav05]

Hi @123abc 

Just wanted to check if there is any update ?