Dropdown list to dynamic Kpi

[jpjust]

All,

I have an  table as below.

UserName          Lastusedindays

Joseph                     25

Tim                            40

Joe                             50

Mary                          30

Mark                          40

I have an KPI that shows number for "More than 30 days". In the case of table above the KPI would show "3"

Now my requirement is , I have to supply an dropdown kind of interface so user can pick the "more than" for eg., "more than 25 days". In this case the kpi should dynamically change to 6.

Can you please let me know how to achieve it?

Thanks

[jwjackso]

You can use the Variable Input Control to create a dropdown list that is assigned to a Variable that you create.  For example if you create variable vDD and use it in the Variable Input Control, you can then create a formula using Set Analysis  in your KPI

=Count({<Lastusedindays={">$(vDD)"}>}UserName)

[jpjust]

Thanks so much for your time, I almost understand. I looked into the Variable Input control properties. Now having expression as below in my case, unsure how to connect these to variable -> Variable Input control -> KPI

Can you please provide me with some suggestions?

Below is my KPI expression that shows count of users more than 45 days.

SUM(If(IF(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),0,
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
)) >45,1,0))

Below is my expression for lastuseddays which returns values such as 25,50,60 etc.,

=If(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),'Never',
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
))

[jwjackso · Accepted solution]

In your KPI,

SUM(If(IF(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),0,
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
)) >$(vDD),1,0))

Add the expression for lastuseddays to your load script, then you KPI becomes much simpler

Count({<Lastusedindays={">$(vDD)"}>}UserName)

[jpjust]

Perfect! That worked. Thanks much for your help