Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Filter values for calculating probability

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.

IdValueDateIs Current
58667107-11-2019FALSE
58667108-11-2019FALSE
58667109-11-2019FALSE
58667110-11-2019FALSE
58667211-11-2019TRUE

 

How can i filter the history Data(Last Two rows only to use that in my chart) in the following Manner.

IdValueDateIs Current
58667110-11-2019TRUE
58667211-11-2019TRUE

 

Thanks in Advance!! Have a Great Day!

DAX
Filter
powerbi

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks az38 

       

      But this does not satisfy my scenario. it fails when 

       

      IdValueDateIs Current
      58667107-11-2019FALSE
      58667108-11-2019FALSE
      58667109-11-2019FALSE
      58667110-11-2019FALSE
      58667212-11-2019FALSE
      58667213-11-2019FALSE
      58667314-11-2019FALSE
      58667315-11-2019TRUE

       

      Den it should return

       

      IdValueDateIs Current
      58667110-11-2019FALSE
      58667213-11-2019FALSE
      58667315-11-2019TRUE

       

      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.

       

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        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 

        58667315-11-2019TRUE

        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