CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
ps0604
ps0604

Reputation: 1071

Concatenate sales month names in DAX

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?

Upvotes: 1

Views: 113

Answers (2)

Sam Nseir
Sam Nseir

Reputation: 12111

Try:

Months when sales happened = 
  var tMonths =
    SUMMARIZE(
      Calendar,
      Calendar[MonthName],
      "rows", COUNTROWS(Sales)
    )
  RETURN CONCATENATEX(FILTER(tMonths, [rows] > 0, [MonthName], ", ")

Upvotes: 1

Marcus
Marcus

Reputation: 4015

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:

https://dax.do/Hn8XjazrX91Mna/

Upvotes: 2

Related Questions