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

  • 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

        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.