True Active in PowerBi

(0) Share

Report

Posted on by Matthew Falle

Hi all,

I am trying to make a chart in PowerBI that shows Active Clients over the past month. Its quite difficult as though if contacts are marked as "Active" they might not have actually had any sub-records created against them.

What I would like to do is be able to say, show me all contact records that have had a sub-record created. Do you know if this is possible?

Kind regards,

Matt

I have the same question (0)

All responses (1)

Answers (0)

Suggested answer

Wahaj Rashid 11,321 on at

Like (1)

Report
Copy link

Link copied!
Hi,

Thank you for your query.

First, you need to which related (sub records) you need to depend on.

Contact has several related records like Opportunites, Quotes Sales Orders, Invoices, Quotes, Cases, Activities, etc.

Define a rule on how you consider a contact active.

For example, lets assume an contact is active if it has at least one activity.

To achieve this, we have two options:

If you are on-premise and have access to the Database, use TSQL to write queries using EXISTS and check if Contact has related records. If you are on the cloud, these kinds of queries are not possible, however, you can load all related tabes to PowerBI and take distinct contacts out of it.

Another approach is to use rollup fields to hold the count of the related records on contact, this will make your queries quite straightforward. For example, count all contacts where new_activitycount > 1.

I have blogged a solution on how to show the activity count of a record using rollup field, you can use similar logic to add related records count on contact level:

Dynamics 365 CE: Check if Lead is contacted – Part 1 | CRM Logs (wordpress.com)

Was this reply helpful? Yes No