cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pBI1234
Helper IV
Helper IV

pass N from LAst N days relative slicer to a measure to count users who DID NOT log in to system

Hi,

I want to use a filter  some way to allow users to select the value for N and filter the measure accordingly:

caculate(count(users),date<=today()-N)

where i want N to be a parameter that can be supplied by user.

If ,the KPI is count of users who accessed system in Last N days,its simple,i use relative slicer and change the N and measure gets filtered accorndingly.Since,it is the negation,i need think i cant say filter/slicer and then do the opposite calculation in the measure.

If it is not possible,am i left with the only way to create measures for each requirement; one for last 7 days,one for last 30 days and so on

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

To let people select a value of N, create a disconnected parameter table that will store all the values of N you're interested in. This table will be used as a slicer. Then create this measure:

 

[# Users] =
var __today = TODAY()
var __dayOffset = SELECTEDVALUE( ParameterTable[DayOffset] )
var __endDate = __today - __dayOffset
var __numOfUsers =
	CALCULATE(
		COUNTROWS( Users )
		DateTable[Date] <= __endDate
	)
return
	__numOfUsers

If nothing is selected from ParameterTable, __endDate will be equal to TODAY(). For the above to work, you have to have a DateTable that is a proper Date table in the model.

 

Best
Darek

View solution in original post

1 REPLY 1
Anonymous
Not applicable

To let people select a value of N, create a disconnected parameter table that will store all the values of N you're interested in. This table will be used as a slicer. Then create this measure:

 

[# Users] =
var __today = TODAY()
var __dayOffset = SELECTEDVALUE( ParameterTable[DayOffset] )
var __endDate = __today - __dayOffset
var __numOfUsers =
	CALCULATE(
		COUNTROWS( Users )
		DateTable[Date] <= __endDate
	)
return
	__numOfUsers

If nothing is selected from ParameterTable, __endDate will be equal to TODAY(). For the above to work, you have to have a DateTable that is a proper Date table in the model.

 

Best
Darek

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors