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

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!

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

      Hi Anonymous ,

      I am sorry my reply was incomplete; I lost some snapshots I included..(something must have gone wrong when posting)

      I understand what you need. Could you please have a look at the following snapshots and give me your feedback if that helps to serve what you need?

       

      You can see that you automatically get the sum of "Value" for the stores corresponding to latest dates for each store given the selection in the slicer. I think this solution is a shortcut, please let me know what you think.

       

      Best regards,

       

    • 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.