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

[miguelsus2000]

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 is a measure value.  It really can't be that difficult to display these (then i'll graph them too).  Thank you... M

[miguelsus2000]

[v-xuding-msft]

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]

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.

BeaconIdentifier	Created	CreatedTimeInSeconds	DevEui	Major	Minor	PartitionKey	RowKey	Rssi	Tenant	Timestamp
0000-10AB4	2019-12-16T22:39:19.906Z	1576557559	70b3d5a4d3100034	100	13360	70b3d5a4d3100034	2.51826E+18	-80	Tenant1	2019-12-17T04:39:19.905Z
0000-10ABV	2019-12-16T22:39:19.906Z	1576557559	70b3d5a4d3100034	100	13387	70b3d5a4d3100034	2.51826E+18	-90	Tenant1	2019-12-17T04:39:19.906Z
0000-10ABW	2019-12-16T22:39:19.906Z	1576557559	70b3d5a4d3100034	97	13388	70b3d5a4d3100034	2.51826E+18	-40	Tenant1	2019-12-17T04:39:19.905Z
0000-10ABW	2019-12-16T22:38:19.868Z	1576557499	70b3d5a4d3100034	97	13388	70b3d5a4d3100034	2.51826E+18	-40	Tenant1	2019-12-17T04:38:19.879Z

This is the able view in POWERBI

[miguelsus2000]

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 · Accepted solution]

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]

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 

[Tahreem24]

@miguelsus2000 ,

Use the below meaure to get the Max value for each group:

Measure = CALCULATE(Max(Table[Value]),ALLEXCEPT(Table,Table[Category]))

Dont forget to hit THUMBS UP and Accept this as a solution if it helps you!

[miguelsus2000]

I forgot to add the table, but the Count per BID is a caluclated measure which MAX doesn't like.

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

Error: Column 'Count per BID' in table 'LoRaBlePayload (2)' cannot be found or may not be used in this expression.

[Tahreem24]

@miguelsus2000 ,

where does Count per BID field come from? Is it coming from same table like 'LoRaBlePayload (2)?

Dont forget to hit THUMBS UP and Accept this as a solution if it helps you!

[miguelsus2000]

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]

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]

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]

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)

    )

)

[miguelsus2000]

Hi  v-xuding-msft and thank you for yoru help.  However, I get a "1" for the measure column.  How did you not get a "1".  Could it be the format you're using?.  See below (I called the above measure is called "max4"):

[miguelsus2000]

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]

One more observation, the total at the bottom also shows "1", does this give any hints?