Grouping the records by having different logics in each field

[yuvarajin]

Here i wanted to do a grouping by EP ID, Requested Date, Request type as one record. As a grouped record each field (other than EP id, Requested Date, Request type ) is having a seperate grouping logics(logic explained below)

1. Each line Due date will be grouped, as a result the max due date will be considered as Grouped Due date

2. Last decision field will be grouped based on below logic

         a) If all are "Approved" then grouped result is "Approved"

         b) If all are "Denied" then grouped result is "Denied"

         c) If all are "Partial Denial" then grouped result is "Partial Denial"

         d) If we are having mixed decision at line level then grouped result is "Partial Denial"

3. At line level if all Re-open/any other flag is showing as Y then group result will be Y else N

4. Decision reason should be concatenated and consider all the different reason in one column by coma seperate value.

Before grouping:

After grouping:

pls assist on this grouping.

[v-yinliw-msft]

Hi @yuvarajin ,

You can try this method:

Create a table:

Result =
SUMMARIZE (
    'Table',
    [EP ID],
    [Request Date],
    'Table'[Request type],
    'Table'[Request Priority],
    "Due Date", MAX ( 'Table'[Due Date] ),
    "Decision Date", MAX ( 'Table'[Decision Date] ),
    "last Decision",
        IF (
            DISTINCTCOUNT ( 'Table'[Last Decision] ) > 1,
            "Partial Denial",
            MAX ( 'Table'[Last Decision] )
        ),
    "Re-open",
        IF (
            CONTAINSSTRING (
                CONCATENATEX ( VALUES ( 'Table'[Re-open(Y/N)] ), [Re-open(Y/N)] ),
                "Y"
            ),
            "Y",
            "N"
        ),
    "Decision reason", CONCATENATEX ( VALUES ( 'Table'[Decision reason] ), [Decision reason], "," )
)

The result is:

Hope these help you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yuvarajin]

Thanks for the quick response , let me try this one. I was actually looking for the solution in Power Query editor for the solution. Is it possible for you assist the same Power Query editor?