Forum Discussion

sibredcat's avatar
sibredcat
Regular Visitor
6 years ago
Solved

filter out old data

Hi All

 

I have a dataset of the following format:

CustomerTimeStatus
Aa1ok
Bb1ok
Cc1nok
Aa2ok
Bb2ok
Cc2ok

This is a live connection dataset, the new data for every customer is coming in every 2 minutes from Azure Stream Analytics. For example, a2-a1=2 mins, but a1 is not the current time, so I can't just filter out all data older than 2 minutes. a1, b1, c1 is in different timezones, but I can convert it to UTC via a measure. Since this is a live dataset, no usage of columns is possible.

 

My question is how can I remove the duplicates from the customer column in such  way that:

- I always have the most recent data for a customer

- In the case when the data with time a1 doesn't come in, the previous one with time a2 is shown. The customers shouldn't disappear completely.

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi sibredcat ,

     

    Make your visual filtered by the measure as below.

    Measure = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table_onpre'[date] ),
        ALLSELECTED ( Table_onpre ),
        VALUES ( Table_onpre[value] )
    )
VAR c =
    MAX ( 'Table_onpre'[date] )
RETURN
    IF ( maxdate = c, 1, BLANK () )

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi sibredcat ,

     

    Make your visual filtered by the measure as below.

    Measure = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table_onpre'[date] ),
        ALLSELECTED ( Table_onpre ),
        VALUES ( Table_onpre[value] )
    )
VAR c =
    MAX ( 'Table_onpre'[date] )
RETURN
    IF ( maxdate = c, 1, BLANK () )

     

     

    • sibredcat's avatar
      sibredcat
      Regular Visitor
      6 years ago

      Thank you so much! It's working perfectly!