Comparing Data Across Two Filters in Power BI

[sirbaklava]

Hello Power BI Community,

I'm working on a project where I need to compare data across two different filters at a time. My dataset includes two key columns: Site and Item Name. The goal is to create a side-by-side comparison table and card that allow filtering and comparing two sets of data simultaneously. Specifically, I aim to:

1. Display a list of items for each selected filter in separate tables.
2. Create a measure that indicaes whether an item from one filter is also present in the other filter.

Current Approach

I used SELECTEDVALUES for the cards and applied custom filtering so each card, table, and filter control its respective side independently. For the measure to check if an item in Filter 1 is found in Filter 2, I wrote the following DAX formula:

FoundInFilter2 =
VAR Filter2ID = [SelectedFilter2]
RETURN
IF (
CALCULATE(
COUNTROWS(
FILTER(
raw_data,
raw_data[site] = Filter2ID &&
raw_data[item_name] = MAX(raw_data[item_name])
)
)
) > 0,
"Yes",
"No"
)

Unfortunately, this measure always returns "Yes" for every item, which isn't accurate.

Request for Assistance

Could you please help me refine my approach? I need a solution where the measure correctly identifies whether an item from Filter 1 is also present in Filter 2, and both sides of the comparison work independently based on the applied filters.

Sample Data:

Sample Data

Site Item Name

Site A	Item 1
Site A	Item 2
Site B	Item 2
Site B	Item 3

Desired Output

• Table 1: Items from Filter 1 (e.g., Site A)
• Table 2: Items from Filter 2 (e.g., Site B)
• Measure: "Yes" if an item in Filter 1 is also in Filter 2, otherwise "No".

Conclusion

I hope this provides a clearer understanding of the issue I'm facing. Any advice or recommendations to improve this setup would be greatly appreciated. Thank you for your support!

[sirbaklava]

 Thank you, Mickey! I ended up being able to solve this by creating 2 identical tables reading off of eachother. Thanks again!

[amitchandak]

@sirbaklava , You can do it using interactions or Two independent tables for site and item

How Interactions Work- Split Page using interactions to compare - https://youtu.be/GIfRKzhMaR4

Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc

[sirbaklava]

Help Pls: Comparing Items Across Two Categories with Dynamic Measures 

Made this second post because I believe this is a much more clear explinations of what's happening.

[sirbaklava]

Amit - thank you so much for replying to my post. I've been struggling with this issue for the last 3-4 days and I'm starting to run out of ideas here. I took a look at both of your videos and I think they're incredibly helpful. I was already tweaking the interactions to make the filters and viz act independent.


I just tried redoing this with a sample dataset with the columns site and items. Here are the 2 measures for the selected values that we're referencing and using in the card which is chosen from a filter:

Measure for Selected Baseline Site: SelectedBaselineSite = SELECTEDVALUE(sample_data[site])

Measure for Selected Comparison Site: SelectedComparisonSite = SELECTEDVALUE(sample_data[site])

Then I put the 2 filters and put site in them.

Next, I put 2 matrices and I have items in them that correspond to the site. Here are the 2 measures I created to get a yes or no on if the item is found in the other selected site:

1. Measure for Baseline Site (checking if the item is in the comparison site):

IsInComparisonSite =
VAR SelectedComparisonSite = SELECTEDVALUE(sample_data[site])
VAR CurrentItem = SELECTEDVALUE(sample_data[item])
RETURN
IF(
COUNTROWS(
FILTER(
ALL(sample_data),
sample_data[site] = SelectedComparisonSite &&
sample_data[item] = CurrentItem
)
) > 0,
"Yes",
"No"
)

Measure for Comparison Site (checking if the item is in the baseline site):

IsInBaselineSite =
VAR SelectedBaselineSite = SELECTEDVALUE(sample_data[site])
VAR CurrentItem = SELECTEDVALUE(sample_data[item])
RETURN
IF(
COUNTROWS(
FILTER(
ALL(sample_data),
sample_data[site] = SelectedBaselineSite &&
sample_data[item] = CurrentItem
)
) > 0,
"Yes",
"No"
)

I made sure that the filters are only affecting their respective site. So filter 1 only affects card 1 and matrix 1, and interactions were turned off for filter 2, card 2, and matrix 2. I did the same thing vice versa.

However, I'm still getting all 'Yes' in the matrices for both using this method. I am not understanding what I am doing wrong. Please help.

[mickey64]

For youre reference.

Step 0: I use your data below.

Step 1: I make 2 tables below.

- 'Sel_Item' Table -

- 'Sel_Site' Table -

Step 2: I make 2 slicers, 2 cards, 2 mesures and a table.

IsInBaselineSite =

VAR SelectedBaselineSite = SELECTEDVALUE('Sel_Site'[Site])

VAR CurrentItem = SELECTEDVALUE('Sel_Item'[Item])

RETURN

IF(

COUNTROWS(

FILTER(

ALL(sample_data),

sample_data[site] = SelectedBaselineSite &&

sample_data[item] = CurrentItem

)

) > 0,

"Yes",

"No"

)

IsInComparisonSite =

VAR SelectedComparisonSite = SELECTEDVALUE('Sel_Site'[Site])

VAR CurrentItem = SELECTEDVALUE('Sel_Item'[Item])

RETURN

IF(

COUNTROWS(

FILTER(

ALL(sample_data),

sample_data[site] = SelectedComparisonSite &&

sample_data[item] = CurrentItem

)

) > 0,

"Yes",

"No"

)

- 'Site' Slicer-

- 'Item' Slicer -

- Card 1 -

- Card 2 -

- Table -

[sirbaklava]

I would provide a PBIX file and sample data of my own but I'm not able to do on here.

[sirbaklava]

Micky thank you so much for replying to my topic.

I see that you created 2 new tables using summarize. I'm having trouble understanding how to apply this to my exact issue. Picture shown below as to what I'm really looking for:

my goal is to have the tables show the items within each site. and then a measure just saying if the item is included in the other selected comparison site.

[mickey64 · Accepted solution]

How about this table?

I also set the filter.

When using the 'SELECTEDVALUE' function, I think the data column you set for the slicer should be different from the base data column.