Forum Discussion
DAX Calculation to create a KPI
gbraunwoodbury,
Have you created relationship between Date Modified field and Date field?
Regards,
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
Lydia, 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,Awesome -- I owe you one!
