cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AlexisOlson Member
Member

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

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_idproduct

1

9/11/2018A
110/11/2018A
110/11/2018B
111/11/2018C
111/11/2018A
29/11/2018C
210/11/2018D
211/11/2018E
211/11/2018A
310/11/2018A
310/11/2018B
311/11/2018A
311/11/2018B
311/11/2018B
410/11/2018A
411/11/2018A
59/11/2018A
510/11/2018B
510/11/2018E
510/11/2018D
511/11/2018C
511/11/2018A
69/11/2018A
610/11/2018A
611/11/2018A

 

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:

 

DesiredOutput.pngDesired 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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

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

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]
    )

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

 

Best Regards,
Dale

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

View solution in original post

7 REPLIES 7
Super User
Super User

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

Hi,

 

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

AlexisOlson Member
Member

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

@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 Super Contributor
Super Contributor

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

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]
    )

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

 

Best Regards,
Dale

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

View solution in original post

Super User
Super User

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

@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.    

 

 

Super User
Super User

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

@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)
          )

 

AlexisOlson Member
Member

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

@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 Super Contributor
Super Contributor

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

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

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 54 members 1,045 guests
Please welcome our newest community members: