Input Snow Range

[ebrownretail]

Hi!

I am trying to find what my snow range is based on the weekly history snow totals. Currently, this is not filling in. Can you please help figure out what to do or whats wrong? I need to take this info for my next step in the analytics process.

Here is my measure:

Find Range history =

CALCULATE(SELECTEDVALUE('Weather Range'[Snow Range]),FILTER('Weather Range', 'Weather Range'[Snow] = [Weekly totals]))

Here is what my table says to find what the snow range is:

Snow	Snow Range
0	0
1	1-3
2	1-3
3	1-3
4	4-8
5	4-8
6	4-8
7	4-8
8	4-8

I have the weekly snow totals below:

Store Number	Week Number	Snow Total	Snow Range
1	202349	1
1	202350	6
1	202403	4

[ebrownretail · Accepted solution]

So i just figured it out by thinkging of a Vlookup in excel and was able to get it to work!

Find Range history =

LOOKUPVALUE('Weather Range'[Snow Range], 'Weather Range'[Snow], [Weekly totals])

[lbendlin]

Join the tables in your data model via the Snow/Snow Total columns.  Then you get the Range for free.

[ebrownretail]

i need it to be the specified range as sales work differently based on different snow amounts.

[Anonymous]

Hi All
Firstly  lbendlin thank you for your solution!
And @ebrownretail ,you can try the treatas function, it should be able to accomplish your needs, you can filter the snowrange according to snowtotal, I hope my solution is helpful to you, I will be honored if I can solve your problem!

Measure = CALCULATE(
MAX('Table (2)'[Snow Range]),TREATAS(VALUES('Table'[Snow Total]),'Table (2)'[Snow]))

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[ebrownretail]

hi!

I just tried this but am getting this error.

Find Range history =

CALCULATE(MAX('Weather Range'[Snow Range]), TREATAS(VALUES('A - Snow Measures', [Weekly totals]), 'Weather Range'[Snow]))

Too many arguments were passed to the VALUES function. The maximum argument count for the function is 1.