Skip to Content
Aug 08, 2020 at 01:49 PM

Correct order of Where, In, Foreach.

54 Views Last edit Aug 08, 2020 at 01:52 PM 4 rev

Hello, I would like to know what is the correct order to use Where, In, Foreach and when put all outside of the calculation and when put it inside, example.

I need to get the max value of the calendar week where the ranking of the measure is less than 5.

This tables are inside a section "Area", I have different areas in my dataset.

[Rank CW] =Rank([CW];[CW])
[Value] =([Value A]/[Value B])+0
[Value (1)] =([Value A]/[Value B]) ForEach([CW]) In ([Area]) 

But this formula gives me the max value for all weeks, is not considering the ranking so I tried to put the where and giving me null results.

Variants that I have tried with no results.

[Value (1)] =([Value A]/[Value B]) ForEach([CW]) In ([Area]) Where([Rank CW]>5)
[Value (1)] =([Value A]/[Value B] ForEach([CW]) In ([Area]) Where([Rank CW]>5))

I expect to get 1.39 if I put where([Rank CW]>5 and [Rank CW<10] for example


sample.png (8.3 kB)