Problems while building a table dynamically

[AlB]

Hi all,

Don't be put off by the length of the post, it's mostly because of the supporting material. This question is derived from this post by @AlexisOlson, although it is not really necessary to read it to answer this.

I want to create a table dynamically within a measure. The table is calculated from Table1 ( see below) and should look like this:

Expected result

That is, we take the customer_IDs, calculate the product list that each has on Table1 and, as last column, we count how many times each product list comes up. In the example we see that A comes up twice and all the rest only once. I don't have problems to create the two first columns but it gets tricky when creating the third column, "Count". If I had a pre-existing table with the first two columns as:

ExistingTable

I would just create the "Count" column easily with something like:

ExistingTable[Count] = CALCULATE(DISTINCTCOUNT(ExistingTable[customer_id]);ALL(ExistingTable[customer_id]))

Now I am trying to replicate that logic in my dynamically created table:

Measure =
VAR _AuxTable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ALL ( Table1[customer_id] );
            "ProdListCol"; CONCATENATEX (
                CALCULATETABLE ( VALUES ( Table1[product] ) );
                Table1[product];
                ", "
            )
        );
        "Count"; WHAT SHOULD I DO HERE to create the column??
    )
RETURN
    Whatever goes here is not relevant now  COUNTROWS ( _AuxTable )

 but the problem is that I cannot directly reference the table created in the inner ADDCOLUMNS and that the use of its columns in expressions is restricted. For instance,  COUNT([ProdListCol]) is not allowed. This limits the options in the code I could use to create the column. How would you solve this?  How would you create that column?

I am particularly interested in the example shown (i.e, with ADDCOLUMNS) and/or in using SUMMARIZECOLUMNS. Not that much in using SUMMARIZE, since @AlexisOlson showed how to do it in his post.

Many thanks in advance.

Table1:

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

[AlexisOlson · Accepted solution]

You can do it using ADDCOLUMNS, but I would still use multiple steps.

Measure = 
VAR _InnerAux =
    ADDCOLUMNS (
        ALL ( Table1[customer_id] ),
        "ProdListCol", CONCATENATEX (
            CALCULATETABLE ( VALUES ( Table1[product] ) ),
            Table1[product],
            ", "
        )
    )
VAR _AuxTable =
    ADDCOLUMNS (
        _InnerAux,
        "Count", COUNTROWS ( FILTER ( _InnerAux, [ProdListCol] = EARLIER ( [ProdListCol] ) ) )
    )
RETURN
    COUNTROWS( _AuxTable )

[v-jiascu-msft]

Hi @AlB,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[AlexisOlson · Accepted solution]

You can do it using ADDCOLUMNS, but I would still use multiple steps.

Measure = 
VAR _InnerAux =
    ADDCOLUMNS (
        ALL ( Table1[customer_id] ),
        "ProdListCol", CONCATENATEX (
            CALCULATETABLE ( VALUES ( Table1[product] ) ),
            Table1[product],
            ", "
        )
    )
VAR _AuxTable =
    ADDCOLUMNS (
        _InnerAux,
        "Count", COUNTROWS ( FILTER ( _InnerAux, [ProdListCol] = EARLIER ( [ProdListCol] ) ) )
    )
RETURN
    COUNTROWS( _AuxTable )

[AlexisOlson · Accepted solution]

Another option would be to use a helper measure

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

inside of the table measure

Measure =
VAR _AuxTable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ALL ( Table1[customer_id] ),
            "ProdListCol", [MeasureProductList] ),
        "Count", COUNTROWS (
            FILTER ( VALUES ( Table1[customer_id] ), [ProdListCol] = [MeasureProductList] )
        )
    )
RETURN
    COUNTROWS( _AuxTable )

If you really need everything in a single measure, then you can always just repeat the helper measure code within the second measure.

Measure =
VAR _AuxTable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ALL ( Table1[customer_id] ),
            "ProdListCol", CALCULATE ( CONCATENATEX ( VALUES ( Table1[product] ), Table1[product], ", " ) )
        ),
        "Count", COUNTROWS (
            FILTER (
                VALUES ( Table1[customer_id] ),
                [ProdListCol]
                    = CALCULATE ( CONCATENATEX ( VALUES ( Table1[product] ), Table1[product], ", " ) )
            )
        )
    )
RETURN
    COUNTROWS(_AuxTable)

[Greg_Deckler]

Well, one way to do this, although I haven't gotten it down to a single measure would be this:

Table = 
VAR __table = 
        ADDCOLUMNS (
            ALL ( Table3[customer_id] ),
            "ProdListCol", CONCATENATEX (
                CALCULATETABLE ( VALUES ( Table3[product] ) ),
                Table3[product],
                ", "
            )
        )
RETURN __table


Measure 3 = 
VAR __prodlistcol = MAX('Table'[ProdListCol])
RETURN
COUNTX(FILTER(ALL('Table'),[ProdListCol] = __prodlistcol),[ProdListCol])


Table 2 = ADDCOLUMNS('Table',"__Count",[Measure 3])

With a little more work, may be able to get this into a single measure perhaps, not a trivial problem. Attached PBIX for reference.