Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
@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),
)
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |