Repeated Time to Access Measure not working with any filters.

[Anonymous]

I have a Power BI HOME page where the current day is selected. So I have built a measure to tell me which app users are regular users.

_Accessing_Daily = CALCULATE(

    DISTINCTCOUNTNOBLANK(AppAccessLog[UserID]),

FILTER('Date','Date'[DateId]=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId])))

    )+0

Then I have built a measure to work out the repeated time for access, calculating the users latest login and their login prior to that if it exists, and giving me the difference in seconds, and then an average.

_RepeatTimeToAccess_Value =

CALCULATE (

  AVERAGEX(

      VALUES(AppUser[AppUserId]),

      DATEDIFF(AppUser[2ndToLastLogin],AppUser[1stToLastLogin],SECOND)

  ),

FILTER('Date','Date'[DateId]=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId])

))

)

Now if I try to get the Repeated Time to Access for the users accessing on that day it gives me a rather large number, which is not correct . It does not allow me to place a visual filter for the Accessing_Daily measure = 1 on the Scorecard visual . Is this a defect or is this just not allowed ? What is the alternative way of doing this ?  Adding thi into the measure && _Accessing_Daily=1 into the measure _RepeatTimeToAccess_Value ? This does not work either.

If I add the users in a summarised table with the Repeated Time to Access measure it gives me the correct answer, but I want it in a Scorecard visual. 

[ImkeF]

Hi @Anonymous  

I find it a bit hard to follow your description. Have you tried moving the SELECTEDVALUE to a variable instead?

_RepeatTimeToAccess_Value =
VAR SelVal = SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId]) )
Return

CALCULATE (

  AVERAGEX(

      VALUES(AppUser[AppUserId]),

      DATEDIFF(AppUser[2ndToLastLogin],AppUser[1stToLastLogin],SECOND)

  ),

FILTER('Date','Date'[DateId] = SelVal

))

If this adjustment doesn't work, please post link to some sample data / mockup of your model.

[Anonymous]

Thank you for the reply. Apologies if the decription is not clear . The problem is that I want the Repeated Time to Access measure only to apply to a subset of users . Theses are the users where Accessing_Daily measure =1.  There is no problem with the filter working for the day just the measure only applying to this subset of users. 

[ImkeF]

Hi @Anonymous 

unfortunately I still don't understand what you're after.

Please check out these guideline on how to explain your requests:

Also make sure to include a proper problem description like described here:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

and

https://social.technet.microsoft.com/wiki/contents/articles/28212.how-to-ask-a-power-bipower-querypower-pivot-question-to-get-a-prompt-accurate-and-helpful-response.aspx

[Anonymous]

An attempt to be hopefully I little clearer where the assistance is sought.

The measure that needs an adjustment is the following:

_RepeatTimeToAccess_Value =
CALCULATE (
AVERAGEX(
VALUES(AppUser[AppUserId]),
DATEDIFF(AppUser[2ndToLastLogin],AppUser[1stToLastLogin],SECOND)
),
FILTER('Date','Date'[DateId]=SELECTEDVALUE('DateSelector'[DateId],MAX('DateSelector'[DateId])
))
)

In a table visual , with 2 fields( AppUserId,_RepeatTimeToAccess_Value Measure) this gives the following . This measure returns the correct result.
AppUserID _RepeatTimeToAccess_Value
1 7
2 8
3 9
Total 8

When I put the measure on its own in a ScoreCard Visual it gives :

3 Billion (seconds)

This is because I am not got anything in the measure to select only the users with the _Accessing_Daily measure set to 1. i.e Daily users. I need to add a filter for a measure.

This is the part of the measure I need assistance for . I have tried to add filters to the _RepeatTimeToAccess_Value Measure and add visual filters but it does not work. So this is what I am looking for some help on ?

[ImkeF]

Yes, that's clearer now.

Could you please create a sample workbook so that I don't have to mock it up myself?

Thanks.

[Anonymous]

I have created a mockup PBIX with Fake Data but cant upload it as I done have an option to do it or permissions on my account. 

Is there any other way of doing it ?

[ImkeF · Accepted solution]

Hi @Anonymous  

unfortunately direct file upload in this forum is limited to staff and superusers.

You'd have to put it into a cloud source and post the link here instead.

Thanks.

[Anonymous]

I was able to work it out by adding some predicates to the filter required. Mockup helps me to focus in on the problem so thanks.