Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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.
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?
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.
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
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.
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
Use the below meaure to get the Max value for each group:
I forgot to add the table, but the Count per BID is a caluclated measure which MAX doesn't like.
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!
Thie Count per Bid is below:
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]
)
)
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.
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"):
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
65 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |