cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vineeta_dahiya
Regular Visitor

How to get Max value of a mesaure based on category

I need help to fix an issue I am facing.Can anyone help here pls 

 

1. I need to get the Maximum transaction Date for KEY column (this KEY willl be similar to the column we give in Group By clause.

2.  Pick the  Schedule_Date which have  Max Transaction_Date derived from Step1. 

3. Get the max Schedule_Date  for a KEY and display it in visual

Dataset view:  these columns and many more columns

Order_NbrItemScheduleKEYTransaction_DateSchedule_Date

100

01

01

100-01-01

24-May-2127-May-21
1000101100-01-0123-May-21 
1000101100-01-0124-May-2128-May-21
1000102100-01-0215-Apr-2115-Apr-21
1000103100-01-0320-Apr-21 
1000101100-01-0325-Apr-2128-Apr-21

 

Required Resultset : these columns and many more columns as well

Order_NbrItemScheduleTransaction_DateFinalSchedule_Date

100

01

01

24-May-2128-May-21
100010123-May-2128-May-21
100010124-May-2128-May-21
100010215-Apr-2115-Apr-21
100010320-Apr-2128-Apr-21
100010125-Apr-2128-Apr-21

 

I tried this doing using Caluclated Column and used Earlier and facing MAJOR PERFORMANCE ISSUE. 

Now I am thinking of doing the same using a measure. 

I am able to reach till Step2 and not finding the way to implement 3rd step. I am using below code. 

FinalSchedule_Date = var A = CALCULATE(
MAX('MCOFF_Sales_Transaction'[TRANSACTION_DATE]),
FILTER( ALL(MCOFF_Sales_Transaction),
MAXX( FILTER( MCOFF_Sales_Transaction , EARLIER( MCOFF_Sales_Transaction[KEY] ) = MCOFF_Sales_Transaction[KEY]),'MCOFF_Sales_Transaction'[TRANSACTION_DATE] )
)
)
Var B = MAXX(MCOFF_Sales_Transaction ,MAX(MCOFF_Sales_Transaction[TRANSACTION_DATE]))

Var C = MAXX(MCOFF_Sales_Transaction ,MAX(MCOFF_Sales_Transaction[SCHEDULE_DATE]))

var D = If(A=B ,C)

Return D

------------------------------------------------------------------------------------------------------------------------

Thanks,

Vineeta

2 ACCEPTED SOLUTIONS
parry2k
Super User
Super User

@vineeta_dahiya try this measure:

 

Max Schedule Date for the key = 
CALCULATE ( MAX ( yourTable[ScheduleKey] ), ALLEXCEPT ( yourTable, yourTable[Key] ) )

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

v-shex-msft
Community Support
Community Support

HI @vineeta_dahiya,

You can try to use the following measure formula if it suitable for your requirement:

FinalSchedule_Date =
CALCULATE (
    MAX ( 'MCOFF_Sales_Transaction'[TRANSACTION_DATE] ),
    ALLSELECTED ( MCOFF_Sales_Transaction ),
    VALUES ( MCOFF_Sales_Transaction[KEY] )
)

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @vineeta_dahiya,

You can try to use the following measure formula if it suitable for your requirement:

FinalSchedule_Date =
CALCULATE (
    MAX ( 'MCOFF_Sales_Transaction'[TRANSACTION_DATE] ),
    ALLSELECTED ( MCOFF_Sales_Transaction ),
    VALUES ( MCOFF_Sales_Transaction[KEY] )
)

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

parry2k
Super User
Super User

@vineeta_dahiya try this measure:

 

Max Schedule Date for the key = 
CALCULATE ( MAX ( yourTable[ScheduleKey] ), ALLEXCEPT ( yourTable, yourTable[Key] ) )

 

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!