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

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
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,784)