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(
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'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.

     

3 Replies

  • 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.