Forum Discussion

IanR's avatar
IanR
Helper III
8 years ago

Count rows in another unrelated table

  I am working in CRM. I would like to create a measure that counts rows in the leads table that contain a particular contact ID from the contacts table. The leads and contacts tables do not have a ...
Need Help
BILASolution's avatar
BILASolution
Solution Specialist
8 years ago

Hi IanR

 

As an alternative you can try this calculated measure.

 

Total Contacts = 

var cont = FIRSTNONBLANK(Contacts[ContactID];1)

return 

COUNTX
(
	FILTER(LeadSet;LeadSet[ParentContactID.Id] = cont);
	LeadSet[ParentContactID.Id]
)

The result is...(In my case)

 

 

 

I hope this helps

 

Regards

BILASolution

IanR's avatar
IanR
Helper III
8 years ago

Hi BILASolution,

First impressipns are that your code seems to work. I'll test it a bit more then mark it as the solution.

Although first of all I'll have to try to understand it! Is it a standard solution? Are there any explanatory articles out there?

Thanks

Ian

  • IanR's avatar
    IanR
    Helper III
    8 years ago

    Hi,

     

    The solution that BILASolution has posted works for individual contacts but I can't get it to work for a group of contacts. For example, if I want to see the average number of leads sent to contacts who purchased in a specific date rang. When I try to include this measure in an average I get an error saying "Column 'Contact Lead Count Measure' cannot be found or may not be used in this expression". Just in case I have created the measure incorrectly I've included the code below. If I don't use average it just gives me the count for the first contact in the group.

     

    Is there a way to get a measure that can be used to count matching instances of a value in another, unrelated table, that can also be used again in sum and average measures? In this instance that's counting leads related to contacts in Dynamics CRM?

     

    This code works for indicvidual contacts:

    Contact Lead Count (Measure) = 
	VAR cont =
    	FIRSTNONBLANK ( ContactSet[ContactId], 1 )
	RETURN
    	COUNTX (
        	FILTER ( LeadSet, LeadSet[ParentContactID.Id] = cont ),
        	LeadSet[ParentContactID.Id]
    		)

    Thanks

     

    Ian