Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calcule similarity between two item

Hi everybody !   Here is my issue : I have this set of data :   SAMU             FEATURE        Y/N SAMU 1          A                     Yes SAMU 1          B                     Yes SAMU 1 ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Sorry for the late reply.

    Add a measure

     

    Measure =
VAR left =
    CONCATENATEX (
        VALUES ( 'left slicer' ),
        'left slicer'[SAMU],
        ","
    )
VAR right =
    CONCATENATEX (
        VALUES ( 'right slicer' ),
        'right slicer'[SAMU],
        ","
    )
RETURN
    IF (
        FIND (
            MAX ( [SAMU] ),
            left,
            1,
            0
        )
            || FIND (
                MAX ( [SAMU] ),
                right,
                1,
                0
            ),
        1,
        0
    )

     

    then modify a measure as below

     

    percent =
VAR P =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[FEATURE] ),
        FILTER (
            'Table',
            [similarity1] = 1
                && [Measure] = 1
        )
    )
        / CALCULATE (
            DISTINCTCOUNT ( 'Table'[FEATURE] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Measure] = 1
            )
        )
RETURN
    IF (
        ISBLANK ( P ),
        0,
        P
    )

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hey v_juan_li !

 

Thx for your help !

 

So the report will be smg like that :

The user chose one SAMU (SAMU is an emergency structure in french) on the left dropdown and an other on the right one.

The goal is to identify the percentage of similarity between the 2 SAMU.

In the set of data i gave here, of course we only have the choice to compare SAMU 1 with SAMU 2.

 

How the calcul of similarty is :

SAMU 1 FEATURE A = YES and SAMU 2 FEATURE A = YES

then FEATURE A is similar (+1)

 

SAMU 1 FEATURE B = YES and SAMU 2 FEATURE B = NO

then FEATURE B is not similar (+0)

 

SAMU 1 FEATURE C = NOand SAMU 2 FEATURE C = NO

then FEATURE B is similar (+1)

 

Similarity : 

Similarity (2) divided by the total of features (3) = 2/3 = 66%

 

So we look into the column "Y/N" for the similarity based on different "SAMU" category for all feature.

 

Thank you

 

 

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous 

Create two tables

left slicer = VALUES('Table'[SAMU])

right slicer = VALUES('Table'[SAMU])

add columns to slicers.

 

Create two measures in table

 

similarity1 =
VAR left =
    CONCATENATEX (
        VALUES ( 'left slicer' ),
        'left slicer'[SAMU],
        ","
    )
VAR right =
    CONCATENATEX (
        VALUES ( 'right slicer' ),
        'right slicer'[SAMU],
        ","
    )
VAR left_yn =
    CALCULATE (
        MAX ( 'Table'[Y/N] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[FEATURE]
                = MAX ( 'Table'[FEATURE] )
                && FIND (
                    [SAMU],
                    left,
                    1,
                    0
                ) > 0
        )
    )
VAR right_yn =
    CALCULATE (
        MAX ( 'Table'[Y/N] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[FEATURE]
                = MAX ( 'Table'[FEATURE] )
                && FIND (
                    [SAMU],
                    right,
                    1,
                    0
                ) > 0
        )
    )
RETURN
    IF (
        left_yn = right_yn,
        1,
        0
    )

percent =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[FEATURE] ),
    FILTER (
        'Table',
        [similarity1] <> 0
    )
)
    / CALCULATE (
        DISTINCTCOUNT ( 'Table'[FEATURE] ),
        ALLSELECTED ( 'Table' )
    )

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Maggi !

     

    Thanks for you amazing job !!!!!!

    Itis realy near to be functonial !!

     

    Indeed, it works with a dataset with only 2 SAMU, but when i do it with my real dataset (which contains 100 SAMU), the percentage screened is wrong.

    I think it's because he counts in the total of similarity with SAMU which are not chosen in the slicers.

    Let's have a look :

    I guess he counts in the total of similarity the number for all SAMU, even if they are not chosen in the slicers.

    That's why the percentage screened is always > 90%.

     

    I dont have the skills to correct the measure you wrote, this is too much complicated for me ...

    I don't know how to attach a document (the .pix) with my post to give you the full dataset ...

     

    EDIT : I think the answer shoud be to modify only the percentage measure to count in the first total only the SAMU chosen in the slicer .. but don't know how to do that !

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous 

      Add some data to your original data example(you could paste here), and share me the expected result for the example.

       

      Best Regards

      Maggie

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hey Maggie,

         

        i hope you're good today.

         

        So here is a better view of my dataset :

         

        And here is the number i want, dpeending on what SAMU i chose in the left and right slicers.

         

         

        thanks !