Count Shared Instances Between One Column and Each Other Column in Same Table

Hello eaelliott

check out this pbix file: https://1drv.ms/u/s!AiiWkkwHZChHjxeS1pgMJ0jlb4Pn

Honestly not too sure what performance will look like with your number of rows and columns, but here is one solution in theory.

First, in Query Editor, unpivot your columns: select all the show columns, right-click, click Unpivot Only Selected Columns. Note, you can change the Attribute and Value column names in the Table.Unpivot formula, but for example purposes, I've left as is. Load into your model (for example purposes, the name of this loaded in table is 'Data').

Second, in Model/DAX, create a crossjoin table of all your shows: in Modeling ribbon, click New Table button and use following formula:

Table = 
FILTER(
    CROSSJOIN(
        SELECTCOLUMNS(VALUES(Data[Attribute]),"Show A",[Attribute]),
        SELECTCOLUMNS(VALUES(Data[Attribute]),"Show B",[Attribute])
    ),
    [Show A]<[Show B]
)

Finally, add a a measure to count overlaps for each pairing in the crossjoin table: first go to Report layer, add a table visual, and add the Table[Show A] and Table[Show B] columns from the crossjoin table just created. Now create the following measure:

Measure = 
COUNTX(
    SELECTCOLUMNS(VALUES(Data[ID]),"IdIter",[ID]),
    COUNTX(
        'Table',
        CALCULATE(
            IF(SUM(Data[Value])=2,1,BLANK()),
            FILTER(Data,Data[ID]=[IdIter] && (Data[Attribute]=[Show A] || Data[Attribute]=[Show B]))
        )
    )
)

Thinking about the steps and the fact you start with 40 columns and 1.1m rows... Unpivot brings you to 44m rows, crossjoin table is ~40x40 = 1600 rows, VALUES(Data[ID]) brings you back to 1.1m...

I think you would get better performance by grabbing list of shows in query editor with Table.ColumnNames, then loading that and original data into model. Then make crossjoin show table similar to before. Then your measure can iterate just through the crossjoin table and the original table of 1.1m to count just the columns per pairing in the crossjoin. Have to set up a somewhat ugly switch to reference the columns (unless there is a better way), but I think the performance would be better.

So...

In query editor (DataOrig is original table in your example):

= List.Skip(Table.ColumnNames(DataOrig))

(Skip is to ignore ID - may have to use a column select or list filter to remove other columns if they are non-show)

Load show list and original table into model.

Crossjoin (Show A x show B) table:

Table2 = 
FILTER(
    CROSSJOIN(
        SELECTCOLUMNS(Shows,"Show A",[Shows]),
        SELECTCOLUMNS(Shows,"Show B",[Shows])
    ),
    [Show A]<[Show B]
)

Load into report table, and do measure:

Measure 2 = 
SUMX(
    Table2,
    CALCULATE(
        COUNTROWS(DataOrig),
        FILTER(
            DataOrig,
            VAR S1 = SWITCH([Show A],"Show 1",[Show 1],"Show 2",[Show 2],"Show 3",[Show 3],"Show 4",[Show 4])
            VAR S2 = SWITCH([Show B],"Show 1",[Show 1],"Show 2",[Show 2],"Show 3",[Show 3],"Show 4",[Show 4])
            RETURN
            S1 = 1 && S2 = 1
        )
    )
)

Would be interested to know if that's any better.

Edit: in Measure 2 originally had a SUMX on DataOrig, but then had a "duh" moment and switched to CALCULATE + COUNTROWS

Hi,

You may download my PBI file from here.

Hope this helps.