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
Highlighted
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
Highlighted
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 Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

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.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)