Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
miguelsus2000
Helper III
Helper III

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

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

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.

View solution in original post

16 REPLIES 16
miguelsus2000
Helper III
Helper III

Presentation2.jpg

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

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.

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

 

Presentation2.jpg

 

 

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.

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.

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.

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,

Tahreem24
Super User
Super User

@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!
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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

 
 
 

image.png

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.
 

 

 

@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!

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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?
 
 
 

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

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]
    )
)

 3.PNG

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.

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.

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)
    )
)
 
 
 
 

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

 
 
 
 

Presentation2.jpg

 

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]

   )

)

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.