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.

     

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!

 
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
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 

     

    Also, you can use the filter measure as a visual filter like below, then you don't need to add it to every measure.

     

    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 

       

      Please see the attached file with the solution included.

       

       

      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 that solved my problem indeed!