Select a field from alternate state in a set analysis

[edemerdjieva]

Hello,

I have a expression which is filtered by a selection in field within alternate state.

The formula is as follows :

=Sum({COMPARATOR<Hour={"<=17"},[Store_id]=$::[Store_Id]>}[Sales])

It selects all sales for the day until 17:00 o'clock (store selection is optional).

The problem is that the hour selection should be dynamic rather than fixed (user interaction).

So, I figured out the formula like this:

=Sum({COMPARATOR<Hour={"<=MaxString({COMPARATOR}[Hour])"},[Store_id]=$::[Store_Id]>}[Sales])

And it'not working. I guess the syntax is wrong and QV could not evaluate MaxString({COMPARATOR}[Hour]).

Can you please help?

[sunny_talwar · Accepted solution]

May be try this

=Sum({COMPARATOR<Hour = {"<=$(=MaxString({COMPARATOR}[Hour]))"}, [Store_id] = $::[Store_Id]>} [Sales])

[edemerdjieva]

Hi Sunny,

Glad to see you around :-).

Thanks, it worked !