Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Help For Filtering the History Data in PowerBi

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!

Need Help
Tips and Tricks
Tutorial Requests

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Mariusz 

       

      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.

       

      Mariusz 

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    6 years ago

    Hi Anonymous ,

    First, Try this measure:

    Is current = 
VAR x = 
CALCULATE(
    LASTDATE(Sheet1[Date]),
    ALL(Sheet1)
)
RETURN
IF(
    MAX(Sheet1[Date]) = x,
    TRUE(),
    FALSE()
)

     

    Second, Try this calculated table:

    Table = 
SUMMARIZE(
    Sheet1,
    Sheet1[Id],Sheet1[Value],
    "Date", MAX(Sheet1[Date])
)

     

    Best regards,
    Lionel Chen

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks v-lionel-msft 

       

      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.

       

      • v-lionel-msft's avatar
        v-lionel-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        I modified the code of the calculated table. 
        Is this what you want?

         

         

        Table = 
SUMMARIZE(
    Sheet1,
    Sheet1[Id],Sheet1[Value],
    "Date", MAX(Sheet1[Date]),
    "Is current", [Is current]
)

         

         

         

        "or If Record/row Value changes. can we change the Is Current value of previous record/row to true. "

        If the date changes as the value changes, the "Is current" formula can achieve your needs.

        If the date doesn't change when the value changes, the "Is current" formula can't take effect.

         

        Best regards,
        Lionel Chen

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.