DAX - Take string from another table WITHOUT a relationship

[bauer24]

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]

Try the VALUES function.

 

[bauer24]

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]

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

 

[JavierGuillen]

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