Displaying most similar inspections based on similarity metric column.

[Anonymous]

Hi there,

I have a dataframe called 'Fact SimilarInspections' that looks similar to this:

| id     | inspection_name | similarity_metric |
|------|--------------------|-------------------|
| 1      | 2092-X                 | 0.177121552      |
| 2      | 2093-X                 | 0.196732766      |
| 3      | 2094-X                 | 0.254706908      |
| 4      | 2095-X                 | 0.172107859      |
| 5      | 2096-X                 | 0.183330358      |

The id column is simply a numeric conversion of inspection_name. The inspection_name is the primary key for an inspection and the similairty_metric is a metric I created to see how similar each inspection is to each other based on a number of explanatory variables. Each row represents one unique inspection.

What I want to do is create a table and slicer in Power BI. In the slicer, I want to be able to select an inspection_name, and when I select one, the table visual should contain the inspections that are most similar to the selected inspection_name, based on their similarity metrics. E.g. If I select inspection_name '2092-X' in the slicer, the table visual should look like:

| id     | inspection_name | similarity_diff                                                       |
|------|--------------------|-----------------------------------------------------|
| 4      | 2095-X                 | abs(0.177121552-0.172107859) =  0.005013693|
| 5      | 2096-X                 | abs(0.177121552-0.183330358) =  0.006208806|
| 2      | 2093-X                 | abs(0.177121552-0.196732766) =  0.019611214|
| 3      | 2094-X                 | abs(0.177121552-0.254706908) =  0.077585356|

Where the similarity_diff is an absolute value (used abs as the straight lines screw up the table) and it is ranked from lowest to highest (most similar to least similar).

My thought is that I need to duplicate my table, because when I use a slicer on an inspection_name for the table, the only thing that will display in the table is one row, for the inspection_name selected. I'm not sure how to approach this afterwards and create a link where I can filter on the table. Any suggestions would be great, thank you :).

[Anonymous]

HI @Anonymous,

I'd like to suggest you extract the inspection_name to create an unconnected table as source of slicer.
Then you can write a measure formula to check the rate from selection value and the fact table records and return the most similar result.

Most similarity=
VAR selectedName =
    MAX ( NewTable[inspection_name] )
VAR selectedSimilarity =
    CALCULATE (
        MAX ( Table1[similarity_diff] ),
        FILTER ( ALLSELECTED ( Table1 ), [inspection_name] = currName )
    )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [nspection_name],
        [similarity_diff],
        "Rate", ABS ( selectedSimilarity - [similarity_diff] )
    )
RETURN
    MINX ( summary, [Rate] )

Similarity Name=
VAR selectedName =
    MAX ( NewTable[inspection_name] )
VAR selectedSimilarity =
    CALCULATE (
        MAX ( Table1[similarity_diff] ),
        FILTER ( ALLSELECTED ( Table1 ), [inspection_name] = currName )
    )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [nspection_name],
        [similarity_diff],
        "Rate", ABS ( selectedSimilarity - [similarity_diff] )
    )
VAR mSimilarity =
    MINX ( summary, [Rate] )
RETURN
    MINX ( FILTER ( summary, [Rate] = mSimilarity ), [nspection_name] )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the response Xiaoxin. I don't think I understand the measure you've created though? There's no similarity_diff column in the data and what is currName supposed to represent? What should the table visual contain when the measure is created?

[Anonymous]

HI @Anonymous,

Sorry, it seems like I copy the wrong version of formulas. The similarity diff mean the similarity_metric field, and the currName should be the variable which I renamed to selectedName.

Most similarity=
VAR selectedName =
    MAX ( NewTable[inspection_name] )
VAR selectedSimilarity =
    CALCULATE (
        MAX ( Table1[similarity_metric] ),
        FILTER ( ALLSELECTED ( Table1 ), [inspection_name] = selectedName )
    )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [nspection_name],
        [similarity_metric],
        "Rate", ABS ( selectedSimilarity - [similarity_metric] )
    )
RETURN
    MINX ( summary, [Rate] )

Similarity Name=
VAR selectedName =
    MAX ( NewTable[inspection_name] )
VAR selectedSimilarity =
    CALCULATE (
        MAX ( Table1[similarity_metric] ),
        FILTER ( ALLSELECTED ( Table1 ), [inspection_name] = selectedName )
    )
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table1 ),
        [nspection_name],
        [similarity_metric],
        "Rate", ABS ( selectedSimilarity - [similarity_metric] )
    )
VAR mSimilarity =
    MINX ( summary, [Rate] )
RETURN
    MINX ( FILTER ( summary, [Rate] = mSimilarity ), [nspection_name] )

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks, that makes a lot more sense. I'm still not sure how I can use the 2 measures to create what I want though. I created the 2 measures in my original Fact table and put them in a table visual. In my slicer, I'm using the 'Inspection_name' from a duplicate, unconnected table like you suggested. When I select an inspection in the slicer, my table visual looks like below.

This is the same for every single inspection_name that I choose for the slicer, the most similar inspection never changes, which shouldn't be true. I also don't want only the top similar inspection to show in the table, but all the other inspections, ordered by the difference in similarity, from lowest to highest difference.