Forum Discussion

sirros_iot's avatar
sirros_iot
Helper III
8 years ago

Filtering a measure by value

Hello all!   I have this seaction on a measure... And I would like to get this values by the same Id Measure A =                     VAR ANT = [.MinAT] - ( INT( [.MinAT] / 86400) * 86400)       ...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Diego,

 

Can you share a sample of your original data AND the expected result? To be honest, I don't  know what is expected.

 

Best Regards,

Dale

sirros_iot's avatar
sirros_iot
Helper III
8 years ago

Hello Dale! Unfortunately the firm doesn't have a pro version..

 

Actually, I was expecting that reply... It's a little bit complicated to explain all the situation. I'm getting the data by direct query, so, I can't use calculated columns and tools like that. I'm trying to do all in measures.

 

This is how I receive the data

I call cycle each line..

sensor = arduino  ||  id = order id || ciclos = total number of cycles by id || contagem = score || _at = timestamp(number in seconds since 1970) from the beginning of the cycle ||  _ts = timestamp from the end of the cycle. 

I can do things like... With the dot(.) is measure

 

 My issue.. How to get the average time of each id? It would be an average apllied at the [.CiclTime]

 

Thank you for the reply, sorry anything, any mistake

Best Regards,

Diego

  • sirros_iot's avatar
    sirros_iot
    Helper III
    8 years ago

    There is no id numbers yet.

     

    Let's say that is showing like..

     

    id

    1

    1

    1

    2

    2

    1

    2

    2

    3

    3

     

    Something like this..

     

    Thanks

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Diego,

     

    Actually, some calculated columns still can be created in the Direct Query mode (not the live connection). You can try to convert [.CiclTime] to a calculated column.

    The second approach could be using a measure like below. ([.CiclTime] is the measure)

     

    Measure =
AVERAGEX (
    SUMMARIZE (
        'table',
        'table'[id],
        'table'[_ts],
        'table'[_at],
        "CiclTime", [.CiclTime]
    ),
    [Cicltime]
)

     

    Best Regards,

    Dale

     

    • sirros_iot's avatar
      sirros_iot
      Helper III
      8 years ago

      It isn't returning something..

       

      Well, I'm getting the data by streaming

      It is portuguese..

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi sirros_iot,

         

        Which approach returned nothing? 

        Did you connect to the Power BI Service datasets in the Desktop? The calculated column won't work in this type of connection. 

        How about creating a table visual with only three columns and one measure?

        [id]  [_ts]  [_at]  [measure]

         

         

        Best Regards,

        Dale