Filter values for calculating probability

[Anonymous]

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!

[az38]

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

LinkedIn

[Anonymous]

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.

[Mariusz]

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.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[az38]

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

LinkedIn