Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic flag with a date slicer

Hi community, Is it possible to create a dynamic column or something with the same functionality? I need this to flag the rows in a table dinamically depending on a date slicer.    So I have a t...
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try something like this.

     

    filter = 
VAR __maxSelectedDate = 
    GROUPBY(
        CALCULATETABLE(
            FILTER( 
                'Table',
                'Table'[Date] <= MAX( 'Dates'[Date] ) 
            ),
            ALLEXCEPT( 'Table', 'Table'[Store] ) ),
        'Table'[Store],
        "@maxDate", MAXX( CURRENTGROUP(), 'Table'[Date] )
    ) 
RETURN 
    CALCULATE( 
        COUNTROWS( 'Table' ),
        KEEPFILTERS( 
            TREATAS( __maxSelectedDate, 'Table'[Store], 'Table'[Date] )
        )
    )

     

    Sum of filter = 
CALCULATE( 
    SUM( 'Table'[Value] ), 
    FILTER( 'Table' , [filter] ) 
)
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous 

 

You can create a measure like below.

Flag = 
VAR __selectedDate = MAX( Dates[Date] )
RETURN 
INT( 
    MAX( 'Table'[Date] ) = 
    CALCULATE(  
        MAX( 'Table'[Date] ),
        'Table'[Date] <= __selectedDate 
    )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Mariusz !

     

    This measure gets the desired result. However I need the flag to use it as a filter in further calculations. Imagine a simple one, the sum of 'Value' (for the last date on each store). If the flag was a column we would do something like this:

    CALCULATE(
         SUM(Table[Value]);
         FILTER(Table; Table[Flag]=1))

    How can I filter in this case as this flag is a measure?

    Thanks!

     

     

    • saraMissBI's avatar
      saraMissBI
      Icon for Resolver I rankResolver I
      6 years ago

      Hi Anonymous ,

       

      You can directly create a quick measure: 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi saraMissBI ,

         

        What I need is to flag a condition to filter in further KPI calculations. This condition as said before is that I only want to take into consideration the last date for each store.

         

        Mariusz proposed this measure ( I just added the last condition for other purposes):

         

         

         

        Flag_ = 
VAR __selectedDate = MAX('Calendar'[Date])
VAR Calculo=
INT( 
    MAX(Facts[Date]) = 
    CALCULATE(  
        MAX(Facts[Date]);
        Facts[Date] <= __selectedDate 
    )
)

RETURN
IF(ISBLANK(SUM(Facts[Value]));BLANK();Calculo)

         

         

         

        Now this allows me to flag the rows in a table visual, but it's not the final purposes as said in the beginning. I create a KPI which is the sum of values for each store on the last date according to slicer, using the previous flag:

         

         

        KPI = 
VAR Flag = [Flag_]

RETURN 
CALCULATE(
   SUM(Facts[Value]);
   FILTER('Facts';Flag=1))

         

         

         

        But the results are not correct when visualized on bar chart or cards:

         

        So  the question is, how do I use the flag to filter on a KPI measure? Or is there any other workaround?


        Thanks!