Forum Discussion
VALUES and table of multiple values error
Hi,
I want to count the discount number of months a customer is invoiced during a year. I have earlier used the VALUES-function which word fine, but now I get the error “A table of multiple values was supplied where a single value was expected”and I dont know why.
Here is example data:
| Invoice date | Customer |
| 201703 | A |
| 201704 | B |
| 201703 | A |
| 201704 | B |
| 201704 | A |
I want to acheive this:
| Customer | Distinct count of invoice date |
| A | 2 |
| B | 1 |
Before I have successfully created the calcalated column or metric as:
'=VALUES(TableX[Invoice date])
But now it does not work, and I get the error message...
/H
4 Replies
Hi agian,
As the next step I would like to use the measure I created for calculating the distinct count and get the average monthly volume:
Raw data column
Raw data column
Raw data column
Invoice date
Customer
Volume
201703
A
10
201704
B
20
201703
A
20
201704
B
30
201704
A
40
Measure
NEW Calculated column or Measure
Customer
Dist. Count number of invoiced months
Average monthly volume
A
2
35
B
1
50
I want need this calculation= Volume (raw date column) / Dist Count number of invoiced months (measure).
When I write this forumla it does not work, I get the error that a single value could not be determined, and it asks for an aggregations such as min, max etc.
Maybe its also not possible to use a measure in a measure?
It is. When using a measure you cannot use naked columns as DAX doesnt know what row(s) you are referring to. You can get away with that in Calculated Columns since calculated columns themselves has a row context, so dax "knows" what you row.
If you create these measures it will work just fine:
Total Volume:=SUM ( Table1[Volume] ) Disct Count of Invoice Months:=DISTINCTCOUNT(Table1[Invoice date] ) Avg Monthly Volume:=DIVIDE( [Total Volume] , [Disct Count of Invoice Months] )
Using DIVIDE is preferred vs / because if there is a divide by zero error DIVIDE will handle it.