Forum Discussion

miguelsus2000's avatar
miguelsus2000
Helper III
6 years ago
Solved

Help with getting the maximum value from calculated table containing measure column

Hi... i only want to display the maximum of each group for the calculated table below.  For example, for 0000-1040I =32, for 1040O=32, 10413=43, etc.  When i use MAX it complains the Counter per BID ...
Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    6 years ago

    Hi miguelsus2000 ,

    Miguel, I tested your sample data. Maybe the following formula can help you. For why you get the sum value, I think it is caused that my sample is too simple. It is filtered by other columns in your actual data.

     

    MAXX = 
MAXX (
    SUMMARIZE (
        FILTER (
            ALLSELECTED ( 'LoRaBlePayload (2)' ),
            'LoRaBlePayload (2)'[Content.BeaconIdentifier]
                IN FILTERS ( 'LoRaBlePayload (2)'[Content.BeaconIdentifier] )
        ),
        'LoRaBlePayload (2)'[Content.BeaconIdentifier],
        'LoRaBlePayload (2)'[PartitionKey],
        "New", [Count per BID]
    ),
    [New]
)

    Best Regards,

    Xue Ding

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

miguelsus2000's avatar
miguelsus2000
Helper III
6 years ago

Thie Count per Bid is below:

Count per BID =
CALCULATE(
    COUNTA('LoRaBlePayload (2)'[Content.BeaconIdentifier]),
    FILTER(
        ALLSELECTED('LoRaBlePayload (2)'[Content.Rssi]),
        ISONORAFTER('LoRaBlePayload (2)'[Content.Rssi], MAX('LoRaBlePayload (2)'[Content.Rssi]), DESC)
    )
)
 
 
This tells me how many readings i've received for each partition key for each content.beaconidentifier.  Now, i only want the max for each group of content.beaconidentifier,t hen show all 3 columns for that max.  Eg. from the calculated table above, i want to show:
 
Content.beaconidentifier         Max Count         Partitionkey
0000-1040l                                32                      7b....58
0000-1040O                               32                     7b....58
0000-10413                                43                     7b....58
 
if i dont need to use Count per Bid, it's ok, but needed a way to verify the max
 
Any ideas?
 
 
 
miguelsus2000's avatar
miguelsus2000
Helper III
6 years ago

Hi... I still have not been able to figure it out, i can use any help you guys can get me.  Thank you..M

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

    Hi miguelsus2000 ,

    You could try to use the function of MAXX. 

    Measure =
CALCULATE (
    MAXX ( 'LoRaBlePayload (2)', [Count per BID] ),
    ALLEXCEPT (
        'LoRaBlePayload (2)',
        'LoRaBlePayload (2)'[Content.BeaconIdentifier]
    )
)

     

    Best Regards,

    Xue Ding

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

    • miguelsus2000's avatar
      miguelsus2000
      Helper III
      6 years ago

      Hi    See below (I called the above measure is called "max4"):

       
       
       
       

      • miguelsus2000's avatar
        miguelsus2000
        Helper III
        6 years ago

         

        Hi v-xuding-msft. Thank you for posting but I’m still getting “1” per table above.

         

        I’ve played with the formats as well. Content.identifier is Text and Counts per BID is Whole number. How are you getting your table? What formats are you using (does it matter?). I also changed the measure name to “max4”.

         

        I’m out of options to try, any other ideas?

         

        max4 =

        CALCULATE (

           MAXX ( 'LoRaBlePayload (2)', [Count per BID] ),

           ALLEXCEPT (

               'LoRaBlePayload (2)',

               'LoRaBlePayload (2)'[Content.BeaconIdentifier]

           )

        )

         

    • miguelsus2000's avatar
      miguelsus2000
      Helper III
      6 years ago

      Hi Xue... I moved the two measures (count per bid and max4 (you provided above)) to a separate table i created to keep it clean.  But i now get an aggregate instead of max (e.g. 34+4=38).  I really appreciate if you can help me.  I cannot reach the same result you do.  Any more ideas?

       

      Thank you so much... Miguel.

       

      max4 =
      CALCULATE (
      MAXX( 'Key Measures', [Count per BID] ),
      ALLEXCEPT (
      'LoRaBlePayload (2)',
      'LoRaBlePayload (2)'[Content.BeaconIdentifier]
      )
      )
       
      Count per BID =
      CALCULATE(
          COUNTA('LoRaBlePayload (2)'[Content.BeaconIdentifier]),
          FILTER(
              ALLSELECTED('LoRaBlePayload (2)'[Content.Rssi]),
              ISONORAFTER('LoRaBlePayload (2)'[Content.Rssi], MAX('LoRaBlePayload (2)'[Content.Rssi]), DESC)
          )
      )