# A table of multiple values was supplied where a single value was expected.



## gand3rson

"A table of multiple values was supplied where a single value was expected."

I am using three tables (one has sales by item in units [sales and cogs], the second has inventory on hand by item in units as well related cost [inventory].   As both these tables have the same item repeated multiple times for different locations, I created a third table of UNIQUE items [item list].  This third table is used to relate the first two tables so that DAX function made be done.


In the third table I calculate "months of sale" at the item level using a DAX as follows (this is actually a simplified version of the formula - the full version adjusts it for months the items was actually avalible for sale):

MOS = sum('Inventory'[On Hand Units])/sum('Sales and COGS '[Sales units])*12  

So far this works great.  Now that I know what my MOS is i want to put the results into "bands" (discretization in BI circles or so I am told).

I have used this Dax formula

Turn Band =if('Sales and COGS '[MOS]<=3,"0-3",if('Sales and COGS [MOS]<=6,"3-6",if('Sales and COGS [MOS]<=9,"6-9",if('Sales and COGS '[MOS]<=12,"9-12",if('Sales and COGS '[MOS]<=18,"12-18","24+"))))))


However while this works I would rather use a formula that links to a 4rth table in power pivot [bands].  That way it is easier to change the bands by changing a table as opposed to a formula.

The table looks like this


BandNameTurn0 - 303 - 636 - 969 - 12912 - 181218 - 241824 + 24

 <colgroup><col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;" width="88"> <col style="width: 48pt;" width="64"> <tbody>

</tbody>
The DAX formula I used to try and come up with the bands for use in the [item] table is this

Turn band =CALCULATE(VALUES('Turn Bands'[BandName]),FILTER('Turn Bands','Sales and COGS '[MOS]>=min('Turn Bands'[Turn])&&'Sales and COGS '[MOS]<max('Turn Bands'[Turn])))
 
When I use that formula I get this error

"A table of multiple values was supplied where a single value was expected."

I admit I am hacking these DAXs together based on internet research so If anyone can help I would be most grateful


----------



## Laurent C

The problem comes from *CALCULATE*(*VALUES*('Turn Bands'[BandName]) ...

CALCULATE expects a scalar expression (an expression return a single value). VALUES actually returns a column expression. If this column only contains 1 scalar value, then the expression will work fine.

Now if you have more than 1 value in your filter context (like in subtotals), then the calcualation will break.

So, you may actually want to rewrite your calculation taking multiple selections into account, or add a safeguard expression like IF(COUNTROWS(VALUES(...)) = 1, ...


----------



## JavierGuillen

The best way to 'band' is to specify, in your lookup table, the min and max value.  In other words:

BandName Min Max
0-3           0     3
4-6           4     6

and so on.

You DAX formula should then be:

CALCULATE(VALUES('Turn Bands'[Band Name]), FILTER('Turn Bands', 'Sales and COGS'[MOS] >= 'Turn Bands'[Min] && 'Sales and COGS'[MOS] <= 'Turn Bands'[Max]) )


----------



## gand3rson

Javier Guillen fine Sir you are a gentleman and a scholar

i was close but obv messed the min max thing up.  All I had to do was remove one of your = signes from the two >= <= and it worked perfectly.


Thank you


----------



## JavierGuillen

Cool.  Glad it helped!


----------

