cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
    )
)

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
    )
)

View solution in original post

Highlighted
taher Regular Visitor
Regular Visitor

Re: count items filtered by DATEDIFF in direct query mode

Hi @Zubair_Muhammad,

 

thanks, it works 🙂

 

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 58 members 1,274 guests
Please welcome our newest community members: