Forum Discussion

sirbaklava's avatar
sirbaklava
Regular Visitor
2 years ago
Solved

Comparing Data Across Two Filters in Power BI

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 AItem 1
Site AItem 2
Site BItem 2
Site BItem 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!

Need Help
  • mickey64's avatar
    mickey64
    2 years ago

    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.

8 Replies

    • sirbaklava's avatar
      sirbaklava
      Regular Visitor
      2 years ago

      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's avatar
        mickey64
        Super User
        2 years ago

        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's avatar
    sirbaklava
    Regular Visitor
    2 years ago

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