Forum Discussion
Having a dynamic flag depending on selected date
// Create a filtering measure that // will return 1 for the restaurants // you want to see and 0 otherwise. // Then, when you display your restaurants // in a table or matrix, just put this // measure in the Filter Pane of the visual // and set the "is" condition to 1. [Show Restaurant?] = IF( ISINSCOPE( Venue[Restaurant] ), var OpenDate = SELECTEDVALUE( Venue[OpeningDate] ) var ComparisonYear = SELECTEDVALUE( CompareYear[CompareYear] ) var BaseMonthNumber = MONTH( MIN( 'Date Table'[Date] ) ) var CutoffDate = DATE( ComparisonYear, BaseMonthNumber, 1 ) var ShouldShow = OpenDate < CutoffDate return INT( ShouldShow ) )
// Create a filtering measure that
// will return 1 for the restaurants
// you want to see and 0 otherwise.
// Then, when you display your restaurants
// in a table or matrix, just put this
// measure in the Filter Pane of the visual
// and set the "is" condition to 1.
[Show Restaurant?] =
IF( ISINSCOPE( Venue[Restaurant] ),
var OpenDate = SELECTEDVALUE( Venue[OpeningDate] )
var ComparisonYear = SELECTEDVALUE( CompareYear[CompareYear] )
var BaseMonthNumber = MONTH( MIN( 'Date Table'[Date] ) )
var CutoffDate = DATE( ComparisonYear, BaseMonthNumber, 1 )
var ShouldShow = OpenDate < CutoffDate
return
INT( ShouldShow )
)Hi daxer,
This seems to be quite close to the solution. But I can still see 1's on the rows before the restaurant opened when I add the fields to a straight table as seen below. The restaurant in Berlin opened in May 2019 (0 on the far right on that row).
And is it possible to add this as an optional filter? So that it is triggered when a button in a box is pressed?
Sincerely,
Ash
Venue[Restaurant] is assumed in my code to be the unique identifier of a restaurant. This field can't have the same value across different restaurants. What's more, a restaurant must have exactly one opening date and if this is not the case in the code I've given you, SELECTEDVALUE will return BLANK and hence the comparison OpenDate < CutoffDate will return TRUE, which means such a row will be displayed.
You have to adjust the code to your model for this to work correctly.
Hi Anonymous,
Can you please share the sample pbix file to test? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng