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

BandName	Turn
0 - 3	0
3 - 6	3
6 - 9	6
9 - 12	9
12 - 18	12
18 - 24	18
24 +	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!