Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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:
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 |
Solved! Go to 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 )
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)
Hi @AlB,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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 )
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)
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |