Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Scenario: I have a history Data which consists of many records. I Just want to Filter the history record if value changes.
so now i just want to filter both
1. before change record(latest) and
2. Changed record
or can i change record field is current value to true if any field value changes?
I Have History Data in PowerBi Query in the Following Manner.
Id | Value | Date | Is Current |
58667 | 1 | 07-11-2019 | FALSE |
58667 | 1 | 08-11-2019 | FALSE |
58667 | 1 | 09-11-2019 | FALSE |
58667 | 1 | 10-11-2019 | FALSE |
58667 | 2 | 11-11-2019 | TRUE |
How can i filter the history Data(Last Two rows only to use that in my chart) in the following Manner.
Id | Value | Date | Is Current |
58667 | 1 | 10-11-2019 | TRUE |
58667 | 2 | 11-11-2019 | TRUE |
Thanks in Advance!! Have a Great Day!
Hi @Anonymous
try a calculated table
Table =
UNION(
summarize(
FILTER('Table1';'Table1'[Is Current]=FALSE());
Table1[Id];Table1[Value];Table1[Is Current];
"Date";MAX(Table1[Date])
);
summarize(
FILTER('Table1';'Table1'[Is Current]=TRUE());
Table1[Id];Table1[Value];Table1[Is Current];
"Date";MIN(Table1[Date])
)
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
Thanks @az38
But this does not satisfy my scenario. it fails when
Id | Value | Date | Is Current |
58667 | 1 | 07-11-2019 | FALSE |
58667 | 1 | 08-11-2019 | FALSE |
58667 | 1 | 09-11-2019 | FALSE |
58667 | 1 | 10-11-2019 | FALSE |
58667 | 2 | 12-11-2019 | FALSE |
58667 | 2 | 13-11-2019 | FALSE |
58667 | 3 | 14-11-2019 | FALSE |
58667 | 3 | 15-11-2019 | TRUE |
Den it should return
Id | Value | Date | Is Current |
58667 | 1 | 10-11-2019 | FALSE |
58667 | 2 | 13-11-2019 | FALSE |
58667 | 3 | 15-11-2019 | TRUE |
It should get updated or saved when value changes
NOTE: Id is Same for all.
or If Record/row Value changes. can we change the Is Current value of previous record/row to true. so that we can filter by
Is Current = true
Thanks,
Sandeep.
Hi @Anonymous
Please see the below DAX expression, hope it is what you looking for.
Table 2 =
VAR __tbl =
GROUPBY(
'Table',
'Table'[Id],
'Table'[Value],
"Date", MAXX( CURRENTGROUP(), 'Table'[Date] )
)
RETURN
CALCULATETABLE(
'Table',
TREATAS(
__tbl,
'Table'[Id], 'Table'[Value], 'Table'[Date]
)
)
I would, however, suggest that you try Power Query for this type of transformation.
Hi @Anonymous
what value should be for the latest value?
in first post you wrote
@Anonymous wrote:Changed record
But in the last example it's also last row
58667 | 3 | 15-11-2019 | TRUE |
if your first task was correct try a caluclated table
Table =
UNION(
summarize(
FILTER(Table1;'Table1'[Value]<calculate(max(Table1[Value]);ALLEXCEPT(Table1;Table1[Id])));
Table1[Id];Table1[Value];Table1[Is Current];
"Date";MAX(Table1[Date])
);
summarize(
FILTER(Table1;'Table1'[Value]=calculate(max(Table1[Value]);ALLEXCEPT(Table1;Table1[Id])));
Table1[Id];Table1[Value];"Is Current";FIRSTNONBLANK(Table1[Is Current];1);
"Date";MIN(Table1[Date])
)
)
do not hesitate to give a kudo to useful posts and mark solutions as solution
User | Count |
---|---|
54 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
81 | |
55 | |
39 | |
20 | |
12 |