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

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