Forum Discussion
summarize data
Hi Anonymous
From you information, MaxID should be the max requisition_event_id per requisition_id, Count of requisition_id should be the count of requisition_id per MaxID, right?
in my test, [Measure] is the MaxID, i could also create a calculated column max to replace it, then create another column count for Count of requisition_id.
count = CALCULATE(COUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max])) max = CALCULATE(MAX([requisition_event_id,]),ALLEXCEPT(Sheet1,Sheet1[requisition_id,]))
Or if you need distintcount, you can use the following formula
distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))
Best Regards
Maggie
AnonymousI am bit confused. Can you post some example how do you want to see the data. I know, you have mentioned in first post. But, it will be great if you post example with the last posted data.
rocky09 HI
my main table is
| d, | requisition_id, | requisition_event_id, | upload_link, | created_by, | updated_by, | created_at, | updated_at |
| 220 | 99 | 1 | 504 | 504 | 07-10-2018 20:06 | 07-10-2018 20:06 | |
| 221 | 100 | 1 | 596 | 596 | 08-10-2018 11:35 | 08-10-2018 11:35 | |
| 257 | 100 | 2 | 1 | 1 | 08-10-2018 20:21 | 08-10-2018 20:21 | |
| 323 | 100 | 4 | 32 | 32 | 09-10-2018 11:21 | 09-10-2018 11:21 | |
| 222 | 101 | 1 | 596 | 596 | 08-10-2018 11:40 | 08-10-2018 11:40 | |
| 256 | 101 | 2 | 1 | 1 | 08-10-2018 20:21 | 08-10-2018 20:21 | |
| 320 | 101 | 3 | 32 | 32 | 09-10-2018 11:20 | 09-10-2018 11:20 | |
| 321 | 101 | 5 | 32 | 32 | 09-10-2018 11:20 | 09-10-2018 11:20 | |
| 223 | 102 | 1 | 596 | 596 | 08-10-2018 11:47 | 08-10-2018 11:47 | |
| 255 | 102 | 2 | 1 | 1 | 08-10-2018 20:21 | 08-10-2018 20:21 | |
| 319 | 102 | 4 | 32 | 32 | 09-10-2018 11:20 | 09-10-2018 11:20 | |
| 224 | 103 | 1 | 419 | 419 | 08-10-2018 12:25 | 08-10-2018 12:25 | |
| 341 | 103 | 2 | 424 | 424 | 09-10-2018 16:29 | 09-10-2018 16:29 | |
| 353 | 103 | 3 | 32 | 32 | 09-10-2018 17:20 | 09-10-2018 17:20 | |
| 369 | 103 | 5 | 606 | 606 | 10-10-2018 10:20 | 10-10-2018 10:20 | |
| 225 | 104 | 1 | 419 | 419 | 08-10-2018 12:27 | 08-10-2018 12:27 | |
| 254 | 104 | 2 | 1 | 1 | 08-10-2018 20:20 | 08-10-2018 20:20 | |
| 318 | 104 | 3 | 32 | 32 | 09-10-2018 11:19 | 09-10-2018 11:19 |
i ahve created measure from above table : Measure name is Max ID
MaxID = MAx('eta requisition_life_cycles'[requisition_event_id])
then i created a visualization with measure max id and drag requisition_id in clustered column chart i get data as
| MaxID | requisition_id |
| 1 | 99 |
| 4 | 100 |
| 5 | 101 |
| 4 | 102 |
| 5 | 103 |
| 4 | 104 |
| 5 | 105 |
| 4 | 106 |
| 5 | 107 |
| 5 | 108 |
| 4 | 109 |
| 5 | 110 |
| 5 | 111 |
| 4 | 112 |
| 5 | 113 |
| 5 | 114 |
| 5 | 115 |
| 5 | 116 |
| 5 | 117 |
but i need data as
| Max ID | Count of requisition_id |
| 1 | 35 |
| 2 | 32 |
| 3 | 14 |
| 4 | 129 |
| 5 | 229 |
| 6 | 17 |
| 7 | 117 |
Hi Anonymous
From you information, MaxID should be the max requisition_event_id per requisition_id, Count of requisition_id should be the count of requisition_id per MaxID, right?
in my test, [Measure] is the MaxID, i could also create a calculated column max to replace it, then create another column count for Count of requisition_id.
count = CALCULATE(COUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max])) max = CALCULATE(MAX([requisition_event_id,]),ALLEXCEPT(Sheet1,Sheet1[requisition_id,]))
Or if you need distintcount, you can use the following formula
distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))
Best Regards
Maggie
v-juanli-msft Hi Maggie, thank you so much.......you made my day. As a new user, this is my first perfect visualization.
still, I don't get. Are you counting requisition_ids against requisition_event_id?
rocky09 yes but against maximum reqisition_event id only.
I am not sure if i understand it correctly. Anyway, try to create New Table from Modeling tab and use this DAX. Make sure to replace the table name and column as per your data.
summarizedata = SUMMARIZE(Table1,Table1[requisition_event_id,],"Count",COUNT(Table1[requisition_id,]))