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.

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

Hi miguelsus2000 ,

As I know, we will get the result that you attached while changing MAXX to SUMX. If you just use my formula but get that, can you please share a simple dummy file that we can understand clearly? 

Use function of SUMX

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 Xue.

 

Yes, I am using MAXX, but the results look like SUMX, which i don't understand why.  Are there any settings which cause MAXX to behave similar to SUMX?  I think the formats are correct as well.

 

Attached is a sample file and also shows the table as it's shown.

           
           
           
           
BeaconIdentifierCreatedCreatedTimeInSecondsDevEuiMajorMinorPartitionKeyRowKeyRssiTenantTimestamp
0000-10AB42019-12-16T22:39:19.906Z157655755970b3d5a4d31000341001336070b3d5a4d31000342.51826E+18-80Tenant12019-12-17T04:39:19.905Z
0000-10ABV2019-12-16T22:39:19.906Z157655755970b3d5a4d31000341001338770b3d5a4d31000342.51826E+18-90Tenant12019-12-17T04:39:19.906Z
0000-10ABW2019-12-16T22:39:19.906Z157655755970b3d5a4d3100034971338870b3d5a4d31000342.51826E+18-40Tenant12019-12-17T04:39:19.905Z
0000-10ABW2019-12-16T22:38:19.868Z157655749970b3d5a4d3100034971338870b3d5a4d31000342.51826E+18-40Tenant12019-12-17T04:38:19.879Z

 

This is the able view in POWERBI

 

 

 

  • miguelsus2000's avatar
    miguelsus2000
    Helper III
    6 years ago

    Hi Xue,

     

    Does the info I provided help in any way?  For some reason, i still cannot break down the 38 into 4 adn 34 you have?

     

    Thank you...Miguel.

    • v-xuding-msft's avatar
      v-xuding-msft
      Community Support
      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

        Hi Xue, Thank you very much this actually worked.   The original calculation was indeed very simple/high level.  I wanted to ask you, if I want to extract the partitionkey mostly used, can simply do a value lookup? 

         

        No worries if you're busy.

         

        Thank you again!

        Cheers,
        M