topic Re: COUNT and USERELATIONSHIP Issue in DAX Commands and Tips

COUNT and USERELATIONSHIP Issue

omd001 — Mon, 31 May 2021 06:58:46 GMT

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.

Re: COUNT and USERELATIONSHIP Issue

selimovd — Mon, 31 May 2021 08:29:04 GMT

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

Re: COUNT and USERELATIONSHIP Issue

omd001 — Tue, 01 Jun 2021 00:32:48 GMT

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.

Re: COUNT and USERELATIONSHIP Issue

omd001 — Tue, 01 Jun 2021 05:54:25 GMT

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.