Hi I have tried many calculate / count functions and cannot get them to work so thought I'd ask here.
I have 3 data tables
Table 1 is called "ExitData" has survey results from people who have resigned. There is a "survey completed date" column here
Table 2 is called "common vwDimEmployee" and has a date listing all the people who have resigned. There is a resignation data column here
Table 3 is called "common vwDimDate" and just has dates listed 1/1/15 to now (plus other columns with month year etc)
I have created a one to many relationship between table 1 survey dates (many) and table 3 date (one)
Also created relationship between table 2 resignation date (many) and table 3 date (one)
I'm trying to calculate a survey completion percentage by dividing the number of surveys complete by the number of people who resigned.
To do this i need 2 countif calculation
first one counting the surveys if the survey date in table 1 matches the date in table 3
second count being the count if resignation date in table 2 matches the date in table 3
Then I'm hoping to chart the survey completion % by "month" using table 3 as the data values and the table 3 "month" field in the axis of the chart.
I have tried various "calculate/countrows/filter" formulas and they are not working For example this formula give no result
Survey Completed = CALCULATE(COUNTROWS('Exitdata'),FILTER('Exitdata','Exitdata'[Survey Completed Date] = firstdate('common vwDimDate'[Date]))
If i can get the counts to work then I should have no trouble getting the completion percentage to work cause its one count divided by the other count.
I have 3 data tables
Table 1 is called "ExitData" has survey results from people who have resigned. There is a "survey completed date" column here
Table 2 is called "common vwDimEmployee" and has a date listing all the people who have resigned. There is a resignation data column here
Table 3 is called "common vwDimDate" and just has dates listed 1/1/15 to now (plus other columns with month year etc)
I have created a one to many relationship between table 1 survey dates (many) and table 3 date (one)
Also created relationship between table 2 resignation date (many) and table 3 date (one)
I'm trying to calculate a survey completion percentage by dividing the number of surveys complete by the number of people who resigned.
To do this i need 2 countif calculation
first one counting the surveys if the survey date in table 1 matches the date in table 3
second count being the count if resignation date in table 2 matches the date in table 3
Then I'm hoping to chart the survey completion % by "month" using table 3 as the data values and the table 3 "month" field in the axis of the chart.
I have tried various "calculate/countrows/filter" formulas and they are not working For example this formula give no result
Survey Completed = CALCULATE(COUNTROWS('Exitdata'),FILTER('Exitdata','Exitdata'[Survey Completed Date] = firstdate('common vwDimDate'[Date]))
If i can get the counts to work then I should have no trouble getting the completion percentage to work cause its one count divided by the other count.
