Forum Discussion
summarize and selectcolumns problems
Hi. I'm trying to SELECTCOLUMNS() to return a table with a single column [customer1] and then use SUMMARIZE() to count how many rows are present for each customer. This sounds fairly simple but I need to do it each month across a date range that is user-filterable.
I built a small demo model in Power BI to investigate the problem I am having with the "temp1" measure. The DAX is:
temp1 =
SUMMARIZE
(
SELECTCOLUMNS -- build a table with 1 column.
(
FILTER
(
cust_with_multiples,
cust_with_multiples[startdate] <= MAX ( 'date'[Date] )
&& cust_with_multiples[stopdate] > MAX ( 'date'[Date] )
)
,"customer1", cust_with_multiples[customer] -- the column to return from the selectcolumns().
)
, [customer1], "prods_pers_cust", COUNTROWS(cust_with_multiples[customer])
)
The "cust_with_multiples" table contains:
| customer | startdate | stopdate |
| a | 01/01/2017 | 26/12/2018 |
| b | 31/01/2017 | 07/04/2018 |
| c | 02/03/2017 | 16/05/2018 |
| c | 01/04/2017 | 03/04/2018 |
| c | 01/05/2017 | 29/10/2018 |
| f | 31/05/2017 | 30/11/2018 |
| g | 30/06/2017 | 22/11/2018 |
| h | 30/07/2017 | 14/05/2019 |
| i | 29/08/2017 | 30/05/2019 |
| j | 28/09/2017 | 15/12/2018 |
| k | 28/10/2017 | 26/10/2019 |
| l | 27/11/2017 | 08/01/2019 |
And the "date" table is built by:
date = CALENDAR(date(2017,1,1), date(2023,1,1))
My desired output from the temp1 measure would be like this:
customer prods_pers_cust
a 1
b 1
c 3
f 1
....
Thanks.
Anonymous , check if this between logic can help
2 Replies
Anonymous , check if this between logic can help