topic Re: Dynamic flag with a date slicer in DAX Commands and Tips

Dynamic flag with a date slicer

Anonymous — Mon, 02 Dec 2019 12:21:02 GMT

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 table with 3 columns: Store, Date and value. Here a simplified example:

Store	Date	Value
A	01/01/2019	1
A	03/03/2019	2
A	05/05/2019	3
B	02/02/2019	4
B	04/04/2019	5
B	08/08/2019	6

For all my calculations I need to use only the values in the last date for each store. So I first created a column that flags the last date for each row:

Store	Date	Value	Flag
A	01/01/2019	1	0
A	03/03/2019	2	0
A	05/05/2019	3	1
B	02/02/2019	4	0
B	04/04/2019	5	0
B	08/08/2019	6	1

The problem here is that this is an static calculation and what I need is this flag to be referenced to a date slicer in the dashboard, so that if the slicer is set to 06/06/2019 for example, the flag would look like this:

Store	Date	Value	Flag
A	01/01/2019	1	0
A	03/03/2019	2	0
A	05/05/2019	3	1
B	02/02/2019	4	0
B	04/04/2019	5	1
B	08/08/2019	6	0

I need the flag to then make some calculations like these:

KPI =
CALCULATE(
SUM(Value)
Filter(Table; Flag=1))

The way the flag is made right now, it considers only the last visit for each store for the current date, but when I go back in time with the slicer it filters the data instead of recalculating the flag.

Is there any way to get this?

I already tried building a table with groupby so that I have the last date for each store but again the same problem, the table is static and doesn't update with the slicer

NOTE = The real data set contains thousands of stores and dates, so any "manual" solution is not useful.

Thank you in advance for your time and help!

Re: Dynamic flag with a date slicer

Mariusz — Mon, 02 Dec 2019 15:43:56 GMT

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.

Re: Dynamic flag with a date slicer

Anonymous — Mon, 02 Dec 2019 16:20:22 GMT

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!

Re: Dynamic flag with a date slicer

saraMissBI — Mon, 02 Dec 2019 18:46:10 GMT

Hi Anonymous ,

You can directly create a quick measure:

Re: Dynamic flag with a date slicer

Anonymous — Tue, 03 Dec 2019 08:55:14 GMT

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!

Re: Dynamic flag with a date slicer

saraMissBI — Tue, 03 Dec 2019 09:59:09 GMT

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,

Re: Dynamic flag with a date slicer

Mariusz — Tue, 03 Dec 2019 10:14:52 GMT

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.

Re: Dynamic flag with a date slicer

Mariusz — Tue, 03 Dec 2019 10:41:35 GMT

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.

Re: Dynamic flag with a date slicer

Mariusz — Tue, 03 Dec 2019 10:49:40 GMT

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.

Re: Dynamic flag with a date slicer

Anonymous — Tue, 03 Dec 2019 17:48:52 GMT

Thanks Mariusz that solved my problem indeed!

Re: Dynamic flag with a date slicer

Anonymous — Wed, 04 Nov 2020 12:48:07 GMT

Woow, that is excelent solution! :-).

One question thought, is there a way to use it with the line chart visual?