COUNT and USERELATIONSHIP Issue

[omd001]

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. 

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[omd001 · Accepted solution]

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]

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.