How can I use a column of a dynamically calculated table in a visual?

[AlexisOlson]

Background: I answered this Stack Overflow question a while ago but never came up with a really satisfactory solution that would scale and I wondered if anyone else has solved something like this.

Sample Data:

customer_id	date_id	product
1	9/11/2018	A
1	10/11/2018	A
1	10/11/2018	B
1	11/11/2018	C
1	11/11/2018	A
2	9/11/2018	C
2	10/11/2018	D
2	11/11/2018	E
2	11/11/2018	A
3	10/11/2018	A
3	10/11/2018	B
3	11/11/2018	A
3	11/11/2018	B
3	11/11/2018	B
4	10/11/2018	A
4	11/11/2018	A
5	9/11/2018	A
5	10/11/2018	B
5	10/11/2018	E
5	10/11/2018	D
5	11/11/2018	C
5	11/11/2018	A
6	9/11/2018	A
6	10/11/2018	A
6	11/11/2018	A

I want to be able to produce a list of different product combinations and the count of customers that have that combination within the filter context defined by any slicers I have.

Sample desired output:

Desired Output

I can generate a calculated table similar to the first case like as follows, but that's obviously not responsive to slicers.

CalculatedTable = 
VAR PerCustomer =
    SUMMARIZE(Table1,
       Table1[customer_id],
       "ProductList",
       CONCATENATEX(VALUES(Table1[product]), Table1[product], ",")
    )
RETURN SUMMARIZE(PerCustomer, [ProductList], "Customers", DISTINCTCOUNT(Table1[customer_id]))

I know you can generate tables dynamically within a measure, however, I don't think it's possible to use a column from a table within a measure as a field in rows of a table or matrix visual.

Since it's not possible to know beforehand what product combinations will exist within an arbitrary filter context, the solution I gave previously required precalculating all possible product combinations, but since this scales exponentially, I'd need over a trillion rows if I had just 40 different products, so this clearly is not a satisfactory general solution.

Can anyone think of an approach that is dynamic but doesn't explode exponentially as the number of products and/or customers grows beyond a small handful?

[Ashish_Mathur]

Hi,

I assume the intentions is to know "how may customers who bought this also bough that".  See if my solution here helps.

[AlexisOlson]

@Ashish_Mathur No, there are some questions like you suggest that are related, but I'm interested more in the technical aspect of the problem right now than any particular interpretation of the data.

[v-jiascu-msft · Accepted solution]

Hi @AlexisOlson,

As we all know, the key point here is the context. So we can give the visual a context. Please download the demo from the attachment. 

1. Create an independent table that will provide the context.

Pontential combination = GENERATESERIES(1, 10000, 1)

2. Create two measures.

Measure 2 =
VAR temp =
    SUMMARIZE (
        'Table1',
        Table1[customer_id],
        "products", CONCATENATEX ( VALUES ( Table1[product] ), [product], "," )
    )
RETURN
    MAXX (
        FILTER (
            ADDCOLUMNS ( temp, "rank", RANKX ( temp, [products] ) ),
            [rank] = MIN ( 'Pontential combination'[Index] )
        ),
        [products]
    )

Measure 3 =
VAR temp =
    SUMMARIZE (
        'Table1',
        Table1[customer_id],
        "products", CONCATENATEX ( VALUES ( Table1[product] ), [product], "," )
    )
RETURN
    COUNTX (
        FILTER (
            ADDCOLUMNS ( temp, "rank", RANKX ( temp, [products] ) ),
            [rank] = MIN ( 'Pontential combination'[Index] )
        ),
        [products]
    )

Best Regards,
Dale

[AlexisOlson]

@v-jiascu-msft Yeah, it looks like there isn't a way to do this without creating an extra column in the visual to supply context unless I do precompute all possible combinations.

Your approach is pretty similar in concept to the second answer I gave on SO.

As I noted there, instead of an arbitrary index of 10000, you can index the possible combinations within a particular filter context by ranking the customers since there cannot be more distinct product combination than there are customers.

[v-jiascu-msft]

Hi @AlexisOlson,

That's embarrassing. You already have a similar better solution. A measure always needs a context for now. That's the key point to solve this puzzle. 

Best Regards,
Dale

[AlB]

@AlexisOlson

Hmm... interesting challenge.

I guess it could be done easily if measures were also allowed to return tables instead of only scalars.

I came up with something similar to (and I think less efficient than)  @v-jiascu-msft's solution, although I do not use the additional index column and only the CustomerID as base for the rows in the matrix visual. I would like to get rid of that but can't see how. On top of that, this is a first version with hardly elegant code, which I am not very happy with, but it seems to work:

1. Place customerID in the rows of a matrix visual

2. Place this measure in values for the product listing:

MeasureProductList = CONCATENATEX(VALUES(Table1[product]);Table1[product];", ") 

3. Place this other measure in values of the matrix for the count of product listings:

MeasureProductListCount = 
VAR _AuxTable =
    ADDCOLUMNS (
        ALL ( Table1[customer_id] );
        "ProdListCol"; CONCATENATEX (CALCULATETABLE ( VALUES ( Table1[product] ) );Table1[product];", ")
    )
VAR _AuxTable2 =
    ADDCOLUMNS (
        _AuxTable;
        "ProdCount"; COUNTROWS (
            FILTER (_AuxTable;
                    [ProdListCol] = CONCATENATEX ( VALUES ( Table1[product] ); Table1[product]; ", " )
            )
        );
        "Occurrence"; COUNTROWS (
            FILTER (_AuxTable;
                    [customer_id] <= EARLIER ( [customer_id] ) && [ProdListCol] = EARLIER ( [ProdListCol] )
            )
        )
    )
RETURN 
    SUMX (_AuxTable2;
          IF ([customer_id] = SELECTEDVALUE ( Table1[customer_id] ) && [Occurrence] = 1;[ProdCount];0)
          )

4. In the visual  level filters, select to show [MeasureProductListCount] when it is not zero. In the end you will have the product combinations with the number of times they come up in total shown at the first (lowest in ascending order) customerID each product list appears.    

[AlB]

@AlexisOlson

This is a version a bit leaner than the previous one:

MeasureProductListCount = 
VAR _AuxTable =
    ADDCOLUMNS (
        ALL ( Table1[customer_id] );
        "ProdListCol"; [MeasureProductList]
    )
VAR _AuxTable2 =
    ADDCOLUMNS (
        _AuxTable;
        "ProdCount"; COUNTROWS (
            FILTER (_AuxTable;
                    [ProdListCol] = EARLIER([ProdListCol])
            )
        );
        "Occurrence"; COUNTROWS (
            FILTER (_AuxTable;
                    [customer_id] <= EARLIER ( [customer_id] ) && [ProdListCol] = EARLIER ( [ProdListCol] )
            )
        )
    )
RETURN 
    SUMX (_AuxTable2;
          IF ([customer_id] = SELECTEDVALUE ( Table1[customer_id] ) && [Occurrence] = 1;[ProdCount];0)
          )