Forum Discussion

NeimadB's avatar
NeimadB
Icon for Helper II rankHelper II
3 years ago

Dynamic filtering on computed DAX table with slicer

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
 
 
 

3 Replies