March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
21 | |
14 | |
10 | |
6 | |
5 |
User | Count |
---|---|
29 | |
22 | |
20 | |
13 | |
10 |