Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @Mario1000
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] )
)
Woow, that is excelent solution! :-).
One question thought, is there a way to use it with the line chart visual?
Hi @Mario1000
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
)
)
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:
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!
Hi @Mario1000
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] )
)
Hi @Mario1000
Also, you can use the filter measure as a visual filter like below, then you don't need to add it to every measure.
Hi @Mario1000
Please see the attached file with the solution included.
Hi @Mario1000 ,
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
48 | |
24 | |
20 | |
14 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |