DAX Calculation to create a KPI

[gbraunwoodbury]

PowerBI Newbie here... trying to do something that would be easy in Excel but can't get my head around it.

Business Problem:  Create an "Update Rate" KPI showing the rate at which contacts in a CRM database are being updated over time. Typically contacts decay at the rate of 20-30% per year, so if contacts are not being updated at at least that rate the database is not healthy.

I have the following available:

Fields

[Contact Name]
[Date Created]
[Date Modified]
[Updated]  -- "yes", "no"

Measure

[Contacts Count] -- =DISTINCTCOUNT([Contact Name])

I need the formula to show the following as a percentage

(DISTINCTCOUNT of contacts when [Updated] = "yes" / (DISTINCTCOUNT of all contacts)

I want to display the KPI as a time series using [Date Created]

Any help is welcome!

[Anonymous]

@gbraunwoodbury,

I am not sure what is the sample data of your table, I make a test in the following table.


Firstly, create a Date table using the DAX below.
Date = CALENDAR(DATE(2016,1,1),DATE(2017,12,31))

Secondly, create relationship between the Date table and original table. Please note that the relationship between DATE Modified field and Date field is inactive.


Thirdly, create the following measure in the original table.

Contacts Count = DISTINCTCOUNT(Table2[Contact Name])
Contacts with yes = CALCULATE(DISTINCTCOUNT(Table2[Contact Name]),USERELATIONSHIP('Date'[Date],Table2[Date Modified]),Table2[Updated]="yes")
Measure 3 = [Contacts with yes]/[Contacts Count]

At last, create visual using the Date field in Date table and the above measures.


If the above steps don’t help, please provide sample data of your table for us  to analyze.


Regards,
Lydia

[gbraunwoodbury]

@v-yuezhe-msft (Lydia)

I understand the solution but it's not yet working for me.

Here's the second measure I created based on your instructions:

Contacts Updated = CALCULATE(DISTINCTCOUNT('Email Data'[FirstName, LastName]),USERELATIONSHIP('Date'[Date],'Email Data'[Date Modified]),'Email Data'[Updated]=TRUE)

PowerBI shows the error: "USERELATIONSHIP function can only use the two columns references participating in relationship."  The query does appear to reference three columns.

Error shown in PowerBI

Here is a screenshot of the sample data

The only difference I see is that my{Updated} field is logical (true/false) as opposed to textual (yes/no)

[Anonymous · Accepted solution]

@gbraunwoodbury,

Have you created relationship between Date Modified field and Date field?



Regards,

[gbraunwoodbury]

Awesome -- I owe you one!

[gbraunwoodbury]

Lydia, thank you for answerig so quickly!  I'll try it out this morning and let you know.