Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

We've captured the moments from FabCon & SQLCon that everyone is talking about, and we are bringing them to the community, live and on-demand. Starts on April 14th. Register now

Reply
Anonymous
Not applicable

summarize data

‎11-10-2018 06:48 AM

 

hi , i have created a measure MaxID and drag column Requisition_id to get a clustered column visualization. the data came as:

 

MaxIDrequisition_id
199
4100
5101
4102
5103
4104
5105
4106
5107
5108
4109
5110
5111
4112
5113
5114
5115
5116
5117

 

actual data i need is given below:

 

Max IDCount of requisition_id
135
232
314
4129
5229
617
7117

 

 

please help.

Message 1 of 13
2,086 Views
0
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support
In response to Anonymous

‎11-12-2018 05:47 PM

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

 

 2.png

Or if you need distintcount, you can use the following formula

distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))

 

Best Regards

Maggie

 

 

 

View solution in original post

Message 12 of 13
2,072 Views
12 REPLIES 12
PattemManohar
Community Champion
Community Champion

‎11-10-2018 07:05 AM
@Anonymous It will be great if you can share the sample data on which you have created the measure. Also, DAX formula that you used to create the measure.




Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Message 2 of 13
2,049 Views
0
Anonymous
Not applicable
In response to PattemManohar

‎11-10-2018 07:14 AM

hi @PattemManohar thanks for reply:

 

Dax formula for Max ID is MaxID = MAx('eta requisition_life_cycles'[requisition_event_id]) 

 

and table is 

id event_name, sequencerequired, requires_upload created_atupdated_at
1Created By User | Awaiting HOD's Approval11022-06-2018 7:2422-06-2018 7:26
2Approved by HOD | Awaiting Store's Approval21022-06-2018 7:2522-06-2018 7:26
3Approved by Store | Awaiting Delivery31022-06-2018 7:2610-07-2018 6:48
4Rejected by Store | Requisition Closed30022-06-2018 7:2706-08-2018 15:02
5Delivered | Requisition Closed31010-07-2018 6:4909-08-2018 8:06
6Rejected by HOD | Requisition Closed20006-08-2018 14:1906-08-2018 14:19
7Auto- approved20025-10-2018 16:3725-10-2018 16:37
Message 3 of 13
2,045 Views
0
Anonymous
Not applicable
In response to Anonymous

‎11-10-2018 07:18 AM

hi @PattemManohar

 

sorry the sample of table data is given below

 

id,requisition_id,requisition_event_id,upload_link,created_by,updated_by,created_at,updated_at
220991 50450407-10-2018 20:0607-10-2018 20:06
2211001 59659608-10-2018 11:3508-10-2018 11:35
2221011 59659608-10-2018 11:4008-10-2018 11:40
2231021 59659608-10-2018 11:4708-10-2018 11:47
2241031 41941908-10-2018 12:2508-10-2018 12:25
2251041 41941908-10-2018 12:2708-10-2018 12:27
2261051 31031008-10-2018 14:1508-10-2018 14:15
2271061 31031008-10-2018 14:1508-10-2018 14:15
2281071 31031008-10-2018 14:1608-10-2018 14:16
2291081 31031008-10-2018 14:1608-10-2018 14:16
2301091 31031008-10-2018 14:1708-10-2018 14:17
2311101 31031008-10-2018 14:1708-10-2018 14:17
2321111 31031008-10-2018 14:1808-10-2018 14:18
2331121 31031008-10-2018 14:1908-10-2018 14:19
2341131 31031008-10-2018 14:2008-10-2018 14:20
2351141 25125108-10-2018 15:0008-10-2018 15:00
Message 4 of 13
2,045 Views
0
Anonymous
Not applicable
In response to Anonymous

‎11-10-2018 07:21 AM

@PattemManohar

 

or you can refer the final data. its more organized. u can see requisition id is repetitive and i am trying to get only max requisiion_event_id from given requisition_id

 

id,requisition_id,requisition_event_id,upload_link,created_by,updated_by,created_at,updated_at
220991 50450407-10-2018 20:0607-10-2018 20:06
2211001 59659608-10-2018 11:3508-10-2018 11:35
2571002 1108-10-2018 20:2108-10-2018 20:21
3231004 323209-10-2018 11:2109-10-2018 11:21
2221011 59659608-10-2018 11:4008-10-2018 11:40
2561012 1108-10-2018 20:2108-10-2018 20:21
3201013 323209-10-2018 11:2009-10-2018 11:20
3211015 323209-10-2018 11:2009-10-2018 11:20
2231021 59659608-10-2018 11:4708-10-2018 11:47
2551022 1108-10-2018 20:2108-10-2018 20:21
3191024 323209-10-2018 11:2009-10-2018 11:20
2241031 41941908-10-2018 12:2508-10-2018 12:25
3411032 42442409-10-2018 16:2909-10-2018 16:29
3531033 323209-10-2018 17:2009-10-2018 17:20
3691035 60660610-10-2018 10:2010-10-2018 10:20
2251041 41941908-10-2018 12:2708-10-2018 12:27
2541042 1108-10-2018 20:2008-10-2018 20:20
3181043 323209-10-2018 11:1909-10-2018 11:19
Message 5 of 13
2,044 Views
0
rocky09
Solution Sage
Solution Sage
In response to Anonymous

‎11-10-2018 08:04 AM

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

Message 6 of 13
2,025 Views
0
Anonymous
Not applicable
In response to rocky09

‎11-10-2018 08:12 AM

@rocky09 HI

 

my main table is 

d,requisition_id,requisition_event_id,upload_link,created_by,updated_by,created_at,updated_at
220991 50450407-10-2018 20:0607-10-2018 20:06
2211001 59659608-10-2018 11:3508-10-2018 11:35
2571002 1108-10-2018 20:2108-10-2018 20:21
3231004 323209-10-2018 11:2109-10-2018 11:21
2221011 59659608-10-2018 11:4008-10-2018 11:40
2561012 1108-10-2018 20:2108-10-2018 20:21
3201013 323209-10-2018 11:2009-10-2018 11:20
3211015 323209-10-2018 11:2009-10-2018 11:20
2231021 59659608-10-2018 11:4708-10-2018 11:47
2551022 1108-10-2018 20:2108-10-2018 20:21
3191024 323209-10-2018 11:2009-10-2018 11:20
2241031 41941908-10-2018 12:2508-10-2018 12:25
3411032 42442409-10-2018 16:2909-10-2018 16:29
3531033 323209-10-2018 17:2009-10-2018 17:20
3691035 60660610-10-2018 10:2010-10-2018 10:20
2251041 41941908-10-2018 12:2708-10-2018 12:27
2541042 1108-10-2018 20:2008-10-2018 20:20
3181043 323209-10-2018 11:1909-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

MaxIDrequisition_id
199
4100
5101
4102
5103
4104
5105
4106
5107
5108
4109
5110
5111
4112
5113
5114
5115
5116
5117

 

 

but i need data as

 

Max IDCount of requisition_id
135
232
314
4129
5229
617
7117

 

Message 7 of 13
2,022 Views
0
v-juanli-msft
Community Support
Community Support
In response to Anonymous

‎11-12-2018 05:47 PM

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

 

 2.png

Or if you need distintcount, you can use the following formula

distintcount = CALCULATE(DISTINCTCOUNT(Sheet1[requisition_id,]),ALLEXCEPT(Sheet1,Sheet1[max]))

 

Best Regards

Maggie

 

 

 

Message 12 of 13
2,073 Views
Anonymous
Not applicable
In response to v-juanli-msft

‎11-13-2018 07:24 AM

@v-juanli-msft Hi Maggie, thank you so much.......you made my day. As a new user, this is my first perfect visualization.

Message 13 of 13
1,815 Views
0
rocky09
Solution Sage
Solution Sage
In response to Anonymous

‎11-10-2018 08:28 AM

still, I don't get. Are you counting requisition_ids against requisition_event_id?

 

 

Message 8 of 13
2,018 Views
0
Anonymous
Not applicable
In response to rocky09

‎11-10-2018 09:07 AM

@rocky09 yes but against maximum reqisition_event id only.

Message 9 of 13
2,010 Views
0
rocky09
Solution Sage
Solution Sage
In response to Anonymous

‎11-10-2018 09:12 AM

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,]))
Message 10 of 13
2,008 Views
0
Anonymous
Not applicable
In response to rocky09

‎11-10-2018 09:31 AM

@rocky09  hi rocky thanks i get the data like 

 

requisition_event id     count

28                                1896

 

but i need bifurcation according to requisition_event_id like you can see in table that requisition_id is repeated according to event id and i need count of requisition_id wrt count of max event id  

Message 11 of 13
1,849 Views
0

Helpful resources

Announcements
New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

Join our Fabric User Panel

Join our Fabric User Panel

Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.

View All