How to group by using categorial values using some business logics

[yuvarajin]

Dear Friends,

i am trying to achieve the results by grouping the decision of Episode ID using some business logic, can anyone help me on this?

Please refer below image for data set and expectation with Logics. KIndly ask me for more questions. 

Thanks,

Yuvi

[ChrisMendoza · Accepted solution]

@yuvarajin -

I attempted using Power Query.

if Table.MatchesAllRows([Grouped], each [Decision] = "Approved") then "Approved" else if Table.MatchesAllRows([Grouped], each [Decision] = "Denied") then "Denied" else if Table.MatchesAllRows([Grouped], each [Decision] = "Partially denied") then "Partially denied" else if Table.MatchesAnyRows([Grouped], each [Decision] = "Pending") then "Pending" else "Partially denied"

[yuvarajin]

Thanks much for the solution. It works as expected. Thanks a ton again 🙂

[ChrisMendoza · Accepted solution]

@yuvarajin -

I attempted using Power Query.

if Table.MatchesAllRows([Grouped], each [Decision] = "Approved") then "Approved" else if Table.MatchesAllRows([Grouped], each [Decision] = "Denied") then "Denied" else if Table.MatchesAllRows([Grouped], each [Decision] = "Partially denied") then "Partially denied" else if Table.MatchesAnyRows([Grouped], each [Decision] = "Pending") then "Pending" else "Partially denied"

[yuvarajin]

Thanks Chris for the quick solutioning and help. This is one of the cool technique i learned from you power query:) i appreciate your support and help on this. Have a good day!

[AlexisOlson]

How about this?

Overall Decision =
VAR DecisionList =
    CALCULATETABLE (
        VALUES ( Table1[Decision] ),
        ALLEXCEPT ( Table1, Table1[Episode ID] )
    )
RETURN
    IF (
        HASONEVALUE ( DecisionList ),
        DecisionList,
        IF ( "Pending" IN DecisionList, "Pending", "Partially denied" )
    )

[yuvarajin]

Thanks Alex for your solutioning. I tried the code and got the error as "The HASONEVALUE function expects a column reference expression for argument '1', but a table expression was used.", can you please help me on that ? Screen shot below for your reference.

[AlexisOlson · Accepted solution]

Sorry. Try COUNTROWS ( DecisionList ) = 1 instead.