Help Pls: Comparing Items Across Two Categories with Dynamic Measures

[sirbaklava]

Goal

I have a dataset at the item level with columns site and item. I want to create a Power BI report where I can compare sites side by side and see which items each site is missing when compared two at a time.

Report Requirements

The report/page should contain:

• 2 Cards: Presenting the selected sites.
• 2 Filters: Used to choose which sites to compare.
• 2 Matrix Visuals: Containing a distinct list of items that belong to the respective chosen site, with a measure indicating if the item is found in the other site we're comparing to.

Steps Taken

1. Added 2 Cards, 2 Filters, 2 Matrices:

   • Put the site field into both filters.
   • Put the item field into both matrices.

2. Created Measures for Selected Sites:

   • Created these measures to display which site was chosen in the card visuals:

SelectedBaselineSite = SELECTEDVALUE(sample_data[site])
SelectedComparisonSite = SELECTEDVALUE(sample_data[site])

• Managed Filter Interactions:

  • Ensured that the filters and visuals are not interfering with each other by configuring the interactions so that:
    • The baseline site filter only affects its respective card and matrix.
    • The comparison site filter only affects its respective card and matrix.

• Created Measures for Item Comparison:

  • Created these measures to check if items exist in the other site being compared:

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"
)

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"
)

Issue

Both of these measures are not working correctly and are showing "Yes" for every single item, even though this is incorrect. I have been trying to resolve this for 3-4 days and I am really losing hope. Here are some screenshots of what it looks like now so you can see how it’s very wrong. I am also attaching the .pbix file and the raw data file to this post.

Request for Help

I have no idea what I am doing wrong. Please, please help me figure out what I am missing. Any guidance or suggestions would be greatly appreciated.

[Samarth_18 · Accepted solution]

Hi @sirbaklava ,

I am unsure about the requirement but Instead of using IsInBaselineSite and IsInComparisonSite separately, create a measure that indicates whether the item is missing in either of the sites. This will help in identifying discrepancies more clearly

ItemComparisonStatus =
VAR IsInBaseline = 
    CALCULATE(
        COUNTROWS('sample_data'),
        'sample_data'[site] = SELECTEDVALUE('sample_data'[site]) &&
        'sample_data'[item] = SELECTEDVALUE('sample_data'[item])
    ) > 0
VAR IsInComparison = 
    CALCULATE(
        COUNTROWS('sample_data'),
        'sample_data'[site] = SELECTEDVALUE('sample_data'[site]) &&
        'sample_data'[item] = SELECTEDVALUE('sample_data'[item])
    ) > 0
RETURN
    SWITCH(
        TRUE(),
        NOT IsInBaseline && IsInComparison, "In Comparison Only",
        IsInBaseline && NOT IsInComparison, "In Baseline Only",
        "In Both"
    )

[v-fenling-msft · Accepted solution]

Hi, @sirbaklava 

Perhaps you can use another method to accomplish your needs that does not require the 'Edit interactons' feature. 

First you need to create two tables 'se1' and 'se2' via New table： 

se1 = 'sample_data'

se2 = 'sample_data'

Then create two corresponding Measures in both tables to show which site is selected in Card visual. 

Table se1:

SelectedBaselineSite = 
SELECTEDVALUE ( se1[site] )

Table se2:

SelectedComparisonSite = 
SELECTEDVALUE ( se2[site] )

Create another Measure to get all the items corresponding to the sites selected by slicer: 

All Item = 
VAR select3 =
    SELECTEDVALUE ( 'se2'[site] )
VAR select4 =
    SELECTEDVALUE ( 'se1'[site] )
RETURN
    IF (
        MAX ( 'sample_data'[item] )
            = CALCULATE (
                MAX ( sample_data[item] ),
                FILTER (
                    'sample_data',
                    'sample_data'[site] = select3
                        || 'sample_data'[site] = select4
                )
            ),
        1,
        0
    )

Finally, a Measure is created to check if there are identical items in the two sites being compared, returning 'true' if there are identical items and 'false' if there are not. 

ItemComparison = 
VAR select1 =
    SELECTEDVALUE ( 'se1'[site] )
VAR select2 =
    SELECTEDVALUE ( 'se2'[site] )
VAR ID1 =
    CALCULATETABLE (
        VALUES ( 'sample_data'[item] ),
        FILTER ( ALLSELECTED ( 'sample_data' ), 'sample_data'[site] = select1 )
    )
VAR ID2 =
    CALCULATETABLE (
        VALUES ( 'sample_data'[item] ),
        FILTER ( ALLSELECTED ( 'sample_data' ), 'sample_data'[site] = select2 )
    )
RETURN
    MAX ( 'sample_data'[item] ) IN INTERSECT ( ID1, ID2 )

This is the final result. 

I have appended the .pbix file to the end, I hope this helps. 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sirbaklava]

Thank you so much. This approach ended up working. Having 2 tables read off eachother is a simple and easy solution. Thanks again.

[Samarth_18 · Accepted solution]

Hi @sirbaklava ,

I am unsure about the requirement but Instead of using IsInBaselineSite and IsInComparisonSite separately, create a measure that indicates whether the item is missing in either of the sites. This will help in identifying discrepancies more clearly

ItemComparisonStatus =
VAR IsInBaseline = 
    CALCULATE(
        COUNTROWS('sample_data'),
        'sample_data'[site] = SELECTEDVALUE('sample_data'[site]) &&
        'sample_data'[item] = SELECTEDVALUE('sample_data'[item])
    ) > 0
VAR IsInComparison = 
    CALCULATE(
        COUNTROWS('sample_data'),
        'sample_data'[site] = SELECTEDVALUE('sample_data'[site]) &&
        'sample_data'[item] = SELECTEDVALUE('sample_data'[item])
    ) > 0
RETURN
    SWITCH(
        TRUE(),
        NOT IsInBaseline && IsInComparison, "In Comparison Only",
        IsInBaseline && NOT IsInComparison, "In Baseline Only",
        "In Both"
    )