Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Having a dynamic flag depending on selected date

Hi,   I'm new at Power BI and got asked to solve a task my co-worker has.   The data model is for a restaurant chain that wants to be able to compare different locations, years and months with a ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    // 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 )
)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.