Forum Discussion

omd001's avatar
omd001
Frequent Visitor
5 years ago
Solved

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( COUNT...
  • omd001's avatar
    omd001
    5 years ago

    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.

     

selimovd's avatar
selimovd
Most Valuable Professional
5 years ago

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's avatar
    omd001
    Frequent Visitor
    5 years ago

    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's avatar
    omd001
    Frequent Visitor
    5 years ago

    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.