VALUES and table of multiple values error

[Anonymous]

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

[Anonymous · Accepted solution]

With Customers on rows, the following should work (as a measure, not a calculated column)

Dist Customer Count:=DISTINCTCOUNT('Tablex [Invoice date] )

[Anonymous]

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?

[Anonymous]

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.  

[Anonymous]

Thanks, why does not a calculated column work?