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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
walnei
Helper III
Helper III

MIN/MAX EDITOR QUERY

I need to create two columns in the query editor, one that brings the minimum time and one that brings the maximum time of each category. According to print. I need to do this in the query editor. M. I know I could do it in DAX but it does not answer me. Can you help me?

 

Sem título.png

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Ok, you can do this in DAX also, which is dynamic and faster.Select New Table under Modelling tab and use this formula :

 

Newtable = GROUPBY('Table_1','Table_1'[Category],"Max_Time_NewColumn",MAXX(CURRENTGROUP(),'Table_1'[Time] ), "Min_Time_New_Column", MINX(CURRENTGROUP(),'Table_1[Time]))

 

This will gie you the max and min values for each category and most importantly without duplicating your data.

 

Thanks
Raj

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi

 

Kind Request : If you post the data in Editable( should be able to do copy/paste)  instead of posting the snapshot of data, that will help the members to help you 🙂

 

1. Duplicate your source table in Query Editor and do Group by "Category" for Max of Time as shown below:Max.PNG

 

 

2. The above step will produce a table with max /min of time for each category.

3. Now, Go to the  original table and do "Merge" with the new Grouped table based on "Category".

4. Expand the merged column and retain the Max & min of time column, you will get the desired output.

 

 

Thanks
Raj

@Anonymous

 

My data has more than 20 million rows, so duplicating and doing merge would be difficult.

Anonymous
Not applicable

Ok, you can do this in DAX also, which is dynamic and faster.Select New Table under Modelling tab and use this formula :

 

Newtable = GROUPBY('Table_1','Table_1'[Category],"Max_Time_NewColumn",MAXX(CURRENTGROUP(),'Table_1'[Time] ), "Min_Time_New_Column", MINX(CURRENTGROUP(),'Table_1[Time]))

 

This will gie you the max and min values for each category and most importantly without duplicating your data.

 

Thanks
Raj

I had done it that way, but I'm having trouble when I add the hours as the print example. Because it does not add more than 24h. In red it's the reSem título2.pngsult I'm getting. But it should be the right green.

Anonymous
Not applicable

The solution which i posted covers the original topic only as the "Total" part was not listed in the original post.

Yes, it really solves the initial. You're right.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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