Hello,
I'm currently facing a problem with a computed table : I want to know the restaurant without any survey for the given period in slicer, and display the list in a table on my report.
I've written a DAX function (thanks to a previous question here) to get this information, but I'm not able to do it dynamically.
Here is my data scheme :
fact.Surveys
RestaurantId
DateId
CompletedSurveyDate
RestaurantName
dimension.Restaurants
RestaurantId
RestaurantName
dimension.Date
DateId
Date
Here is the first code, but unfortunately, it's not dynamic
ComputedRestaurantWithoutCompletedSurveyQuestions =
VAR CompletedSurveyQuestionsRestaurants =
DISTINCT (
SUMMARIZE(
FILTER ( 'CompletedSurveyQuestions', 'CompletedSurveyQuestions'[CompletedSurveyDate] >= MIN ( 'dimension Date'[DateDate] )
&& 'CompletedSurveyQuestions'[CompletedSurveyDate] <= MAX ( 'dimension Date'[DateDate] )
), 'CompletedSurveyQuestions'[RestaurantName] )
)
VAR ListRestaurants = VALUES ( 'dimension Restaurants'[RestaurantName] )
VAR NoCompletedSurveyQuestionsRestaurants = EXCEPT ( ListRestaurants , CompletedSurveyQuestionsRestaurants )
RETURN NoCompletedSurveyQuestionsRestaurants
So atfer that, I've created 2 measures in my dimension Date table in order to get Min / Max selected values in my slicer, change my DAX table code, but it also doesn't work
Date Selected Max =
CALCULATE(
MAX( 'dimension Date'[DateDate] ),
ALLSELECTED( 'dimension Date'[DateDate] )
)
Date Selected Min =
CALCULATE(
MIN( 'dimension Date'[DateDate] ),
ALLSELECTED( 'dimension Date'[DateDate] )
)
ComputedRestaurantWithoutCompletedSurveyQuestions =
VAR CompletedSurveyQuestionsRestaurants =
DISTINCT (
SUMMARIZE(
FILTER ( 'CompletedSurveyQuestions', 'CompletedSurveyQuestions'[CompletedSurveyDate] >= ( 'dimension Date'[Date Selected Min] )
&& 'CompletedSurveyQuestions'[CompletedSurveyDate] <= ( 'dimension Date'[Date Selected Max] )
), 'CompletedSurveyQuestions'[RestaurantName] )
)
VAR ListRestaurants = VALUES ( 'dimension Restaurants'[RestaurantName] )
VAR NoCompletedSurveyQuestionsRestaurants = EXCEPT ( ListRestaurants , CompletedSurveyQuestionsRestaurants )
RETURN NoCompletedSurveyQuestionsRestaurants
How can I make this table dynamic, depending on the date slicer ?
Thank you
