cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
taher Regular Visitor
Regular Visitor

count items filtered by DATEDIFF in direct query mode

Hi all,

 

I am using direct query and have this problem:

I want to count the useres which are not active since three months. Because of that I've created a measure which claculates the diffrence between today and the last active day for the users, so 1 for  not active , 0 for active .

Is inactive user since 90 days = IF( DATEDIFF(MAX('table'[TimestampUtc]);NOW();DAY)>90;1;0)

 

 It works well ! 

But when I need to create other measue which calculates the the count like this, I get this error:

count of inactive users = CALCULATE(DISTINCTCOUNT('table'[UserId]);[Is inaktiver Nutzer seit 90 Tagen]=1)

Unbenannt2.PNG

 

I have read that, this formula is not supported in direct query, so I went another way with a table like this but it doesn't count the number of 1 ( it seems it shows the average):

 

Unbenannt.PNG

What should I do !!

Thanks for Help 🙂

Taher

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: count items filtered by DATEDIFF in direct query mode

@taher

 

Give this a shot

 

count of inactive users =
CALCULATE (
    DISTINCTCOUNT ( 'table'[UserId] ),
    FILTER (
        ALLSELECTED ( 'table'[UserId] ),
        [Is inaktiver Nutzer seit 90 Tagen] = 1
    )
)
Try my new Power BI game Cross the River

View solution in original post

2 REPLIES 2
Super User
Super User

Re: count items filtered by DATEDIFF in direct query mode

@taher

 

Give this a shot

 

count of inactive users =
CALCULATE (
    DISTINCTCOUNT ( 'table'[UserId] ),
    FILTER (
        ALLSELECTED ( 'table'[UserId] ),
        [Is inaktiver Nutzer seit 90 Tagen] = 1
    )
)
Try my new Power BI game Cross the River

View solution in original post

taher Regular Visitor
Regular Visitor

Re: count items filtered by DATEDIFF in direct query mode

Hi @Zubair_Muhammad,

 

thanks, it works 🙂

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)