Variables inside Set Analysis

[bkn]

I have a variable which store expression. I need to use that variable in Set Analysis.

Variable = vData

Eg.  =SUM({<Account={"Prime"}>}vData) // this does not work

[ramoncova06]

ok then try with

If(UnbilledMonthName='FEB', aggr(SUM({<UnbilledMonthName={'NOV'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='MAR', aggr(SUM({<UnbilledMonthName={'DEC'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='APR', aggr(SUM({<UnbilledMonthName={'JAN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='MAY', aggr(SUM({<UnbilledMonthName={'FEB'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='JUN', aggr(SUM({<UnbilledMonthName={'MAR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='JUL', aggr(SUM({<UnbilledMonthName={'APR'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='AUG', aggr(SUM({<UnbilledMonthName={'MAY'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='SEP', aggr(SUM({<UnbilledMonthName={'JUN'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='OCT', aggr(SUM({<UnbilledMonthName={'JUL'},UnbilledFYYear={'$(vFYCurrentYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='NOV', aggr(SUM({<UnbilledMonthName={'AUG'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='DEC', aggr(SUM({<UnbilledMonthName={'SEP'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)

,If(UnbilledMonthName='JAN', aggr(SUM({<UnbilledMonthName={'OCT'},UnbilledFYYear={'$(vFYPriorYear)'}>}[Unbilled Current Quarter]),UnbilledMonthName)))))))))))))

[Peter_Cammaert]

Your set expression produces an empty set, unless ID can contain the string

"=Rank(Aggr(SUM([Current Quarter]),[ID]))<=20"

This is a string and not an expression that you want to be evaluated and produce 0 (false) or -1. (true).

First test without set expression and with the big IF instead of a variable. If that works, replace the big IF by the variable substitution $(vData). If that works, add a correct set expression. That way, you'll gradually build up a very complex anf functioning expression without loosing track of where exactly things start to go wrong.

Best,

Peter