Visualize links between data

[TheBigFrench]

Hi,

How can visualize the link between data, in a single table.

Here is a very simple example  :

COL1	COL2
A	XX
B	XX
C	XX
A	YY
E	YY
F	XX
F	YY
H	YY
I	XX
J	YY
M	YY
M	YY
M	XX
N	XX

In this exemple, the cells in bold and green are common between YY and XX.

What type of visualise (or calculation two), can I use to only see the value in common ?

I tries to used the "forced directed graph" (from Microsoft), but I don't know how to filter value in common only (so the graph is unreadable).

The value are not that, but my table is realy like that, with millions of lines, but with only few in common, I want to visualize.

Thanks for your help and advices

[Daniel29195 · Accepted solution]

@TheBigFrench 

the one on the right,  when you selected from slicer, xx  and yy , it will filter out and leave only the values that have xx and yy in comon . 

Measure 10 =

var filters = countrows(ALLSELECTED(table31[COL2]))

var datasource =

filter(

ADDCOLUMNS(

    SUMMARIZE(

        table31,

        table31[COL1]

    ),

    "@x" ,  CALCULATE(DISTINCTCOUNT(table31[COL2]))

),

[@x] = filters

)

RETURN

    CALCULATE(

        COUNTROWS(table31),

        KEEPFILTERS(datasource)

    )

## -############

## -############

## -############

the one on the left , will show which values have common data from the other column .  ( not affected by slicer) (this is somewhat complex ) 

Measure 9 =

var datasource =

ADDCOLUMNS(

    ALLSELECTED(table31[COL1]),

    "@concat"  ,

    CALCULATE(

        CONCATENATEX(

            WINDOW(

                0,

                ABS,

                -1,

                ABS,

                SUMMARIZE(

                    table31,

                    table31[COL1],

                    table31[COL2])

                    ,ORDERBY(table31[COL2] ,  asc )

            ),

            table31[COL2], ","

        )

    )

)

var selected_current_filter_context =  SELECTEDVALUE(table31[COL1])

var current_row_data =

SELECTCOLUMNS(

FILTER(

    datasource,

    table31[COL1] = selected_current_filter_context

),

[@concat]

)

var result =

FILTER(

    datasource,

    [@concat] = current_row_data

)

RETURN CONCATENATEX(result, table31[COL1] , ", " )

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🤠

[TheBigFrench]

I tried creating a table (with two formulas : , with the agregation and the count (STEP 3 above), but on STEP 4, it is always filtered by the row context : 

NewTable = DISTINCT('OrigineTable'[col1]) 

And 

Totals =

   SUMX(

      VALUES('OrigineTable'[col1]),

      CALCULATE(COUNT('OrigineTable'[col2]))

   )

But when I use the Totals in NEWTable linked with OrigineTable (on col1), it won't work, because of the row context !

[TheBigFrench]

Hi @Greg_Deckler ,

Thanks for your proposition. It is not working, probably because of the line context. But it may me think of an other way : how can I exclude the row context from the calculation.

I am thinking of using the DISTINCT on COL1, after being SUMMERIZED with your formula. Something like that, steps by steps : 

Thanks for your help.

Regards,

[Greg_Deckler]

@TheBigFrench Well, you could create a measure that you can filter on. Something as simple as:

Common = COUNTROWS(SUMMARIZE('Table', [COL1], [COL2]))

You could then filter for when Common = 2