Using the AdventureWorks dataset, I need to create the following report in Power BI: First column has CustomerKey, second column Total Sales of this customer, and third column the distinct concatenation of the names of the months when that customer made a purchase.
For the last column, I have the following DAX measure:
Months when sales happened = CONCATENATEX(VALUES(Sales[OrderDate].[Month]), Calendar[MonthName])
With VALUES() I get the distinct list of months where the customer made the purchase, and Calendar[MonthName] returns the month name. Last, CONCATENATEX concatenates the names of the months.
The error I get in the measure is:
A single value for column 'MonthName' in table 'Calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
How to make this work?
Your measure is giving you an error stating it can't determine a single value for Calendar[MonthName] for a given value in the related auto date/time table of your Sales[OrderDate] column.
Edit: Here is a new measure that gives you the months where sales happened for a given evaluation context.
Months when sales happened =
VAR _sales_months =
SUMMARIZE (
'Date',
'Date'[Month],
'Date'[Month Number],
"Sales", COUNTROWS ( Sales )
)
VAR _filtered =
FILTER ( _sales_months, [Sales] > 0 )
RETURN
CONCATENATEX ( _filtered, 'Date'[Month], ", ", 'Date'[Month Number], ASC )
This assumes that order date is the active relationship between Date and Sales tables.
See example on dax.do: