Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register 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! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
18 | |
15 | |
13 | |
10 | |
8 |
User | Count |
---|---|
24 | |
17 | |
11 | |
11 | |
10 |