Forum Discussion

Hettic's avatar
Hettic
Frequent Visitor
5 years ago

Losing filter context via group by/summarize

I work with different data than sales data, but for everyone to be able to understand I used a contoso data set for it.

What I would like to achieve is to count the number of unique customers that bought more than one product.

 

 

CustomersMoreProducts =

VAR MaxDate = MAX('Date'[Date])
VAR CustomersMoreProducts =
--Unique customers with more than one product in period.
CALCULATETABLE(
FILTER(SUMMARIZE(Sales,
Customer[CustomerKey],
"#Products", DISTINCTCOUNT(Sales[ProductKey])
),
[#Products] > 1),
DATESBETWEEN ( 'Date'[Date], DATE(2008,1,1), MaxDate )
)

RETURN
COUNTROWS(CustomersMoreProducts)

 


However, I would like to analyse it by last product.
So imagine a table visual with productname (=last product) and the measures(=customers who bought more than one product). 

I know that in the filter context there is only one (counted) product so therefore the measure is returning BLANK.

Could someone point me in the right direction.

I also have another code sample in which I first calculate a table variable with the following output:

CustomerKey, LastBoughtProduct, LastBoughtDate.
Then I tried to use TREAT AS to assign field LastBoughtProduct to Product[ProductKey], however not the result I expected.

 

 

2 Replies

  • Hettic , Try like

     

    CustomersMoreProducts =

    VAR MaxDate = MAX('Date'[Date])
    return
    COUNTROWS(
    FILTER(SUMMARIZE(Sales,
    Customer[CustomerKey],
    "#Products", calculate(DISTINCTCOUNT(Sales[ProductKey]),DATESBETWEEN ( 'Date'[Date], DATE(2008,1,1), MaxDate ))
    ),
    [#Products] > 1),

    )

    • Hettic's avatar
      Hettic
      Frequent Visitor

      Tried it, but also in this case the grand total is calculated however the context at product name is lost, resulting in a blank value for each product name.