Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
IanR
Helper III
Helper III

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 relationship. When I try to set one up Power BI complains that this would create ambiguity with another table that both are already related to.

I have created a calculated column in the contacts table:

 

Contact Lead Count = 
CALCULATE (
    COUNTROWS ( LeadSet ),
    FILTER ( LeadSet, LeadSet[ParentContactId.Id] = ContactSet[ContactId] )
)

This is useful but I cannot use this to ask questions relating to milestones in the selling process, such as ‘how many leads were there between first contact and first purchase?’.

Is there a way to do this? The code for the calculated column does not work in a measure, which I understand.

 

Thanks
Ian

4 REPLIES 4
BILASolution
Solution Specialist
Solution Specialist

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)

 

rr.png

 

 

I hope this helps

 

Regards

BILASolution

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

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

Greg_Deckler
Super User
Super User

I would look at adjusting your table relationships so that you can create the necessary relationship. Generally this means adjusting the direction of your relationships. Can you post a picture of your relationships?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.