Forum Discussion
DAX Calculation to create a KPI
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!
gbraunwoodbury,
Have you created relationship between Date Modified field and Date field?
Regards,
5 Replies
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,
LydiaLydia, thank you for answerig so quickly! I'll try it out this morning and let you know.
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)
gbraunwoodbury,
Have you created relationship between Date Modified field and Date field?
Regards,
