# DAX - Take string from another table WITHOUT a relationship



## bauer24 (Aug 22, 2012)

Hi,

I have two tables.  One is daily data (A) the other (B) has two date columns, a START and END date and a third column that contains some string.

E.g. - table B might look like so:

START,END,TEMP
01/01/2012,01/04/2012,"COLD"
02/04/2012,05/06/2012,"HOT"

etc.

If the temp column were *numeric* I could use sumx() and two filters to get the values across:

=sumx(filter(filter(B,DATEVALUE(Data[Date])>=B[Start]),DATEVALUE(Data[Date])<=B[End]),B[TEMP])

but i'm stuck as to how I get a string across.  I haven't made a relationship as it didn't seem appropriate for this problem.

Any advice is much appreciated


----------



## ruve1k (Aug 22, 2012)

Try the VALUES function.


----------



## bauer24 (Aug 23, 2012)

ruve1k said:


> Try the VALUES function.



Afraid I couldn't work out how to use the values function here.  A friend of mine found a working solution though, it's available here.  It uses the TOPN function (which I'd never even heard of).


----------



## ruve1k (Aug 23, 2012)

Perhaps something like this:
CALCULATE(VALUES(B[TEMP]), _Filter_Expression_ )


----------



## JavierGuillen (Aug 28, 2012)

Use the following DAX expression as a calculated column: (here I assume 'StringLookup' is the table you specify above and 'Data' is the daily data table)

=CALCULATE(VALUES(StringLookup[temp] ), FILTER( StringLookup, Data[date] >= StringLookup[Start] && Data[date] < StringLookup[end] ) )


Javier Guillen
Senior BI Consultant
PowerPivot Blog: http://javierguillen.wordpress.com
Twitter: @javiguillen


----------

