cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omd001
Frequent Visitor

COUNT and USERELATIONSHIP Issue

I would like to count the number of projects with status "Completed" based on an inactive date relationship "Actual Completion Date".

I used the following command:

Completed =
CALCULATE(
COUNTROWS(FILTER('FACT Project','FACT Project'[Status] = "Completed")
),
USERELATIONSHIP('DIM Dates'[Date],'FACT Project'[Actual Completion Date]))+0
It's returning me 0 instead of 14 as a count for the month of May. 
 
omd001_0-1622444291844.png

 

1 ACCEPTED SOLUTION
omd001
Frequent Visitor

Hi Denis,

I've managed to fix the issue using a different approach and it worked!

Completed = CALCULATE([Total Projects], 'FACT Project'[Status]="Completed", USERELATIONSHIP('DIM Dates'[Date], 'FACT Project'[Actual Completion Date]))

 I basically created a new measure to count all the projects first, and thereafter filter it with the status then use the inactive relationship.

 

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

Hey @omd001 ,

 

use the FILTER as an argument for CALCULATE and just count the rows of the basic table. This will be filtered by the arguments in CALCULATE. 

So try the following approach:

Completed =
CALCULATE(
    COUNTROWS( 'FACT Project' ),
    FILTER(
        'FACT Project',
        'FACT Project'[Status] = "Completed"
    ),
    USERELATIONSHIP ( 'DIM Dates'[Date], 'FACT Project'[Actual Completion Date] )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
omd001
Frequent Visitor

Hi Denis,

I've managed to fix the issue using a different approach and it worked!

Completed = CALCULATE([Total Projects], 'FACT Project'[Status]="Completed", USERELATIONSHIP('DIM Dates'[Date], 'FACT Project'[Actual Completion Date]))

 I basically created a new measure to count all the projects first, and thereafter filter it with the status then use the inactive relationship.

 

omd001
Frequent Visitor

Thanks Denis.

It's strange that it only returned 2 rows of data and I am expecting more. I have no filters causing this so I wonder why it is not returning the expected results.

 

omd001_0-1622507524949.png

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors