Compare items in a column matching a similar value from a different column

[rjtop]

Part of my researching is trying to explain what I'm looking for, so I hope my subject wasn't too confusing. 

I have a huge table with various software titles and user names and a pass/fail analysis for each month.  Basically, I need to look at each User(John) and evaluate each Software(SoftwareA, SoftwareB, etc.) individually and if there are any "Pass" values, then it is a Pass, otherwise, it fails.

So, in the example below, John's Software A would be a Pass (even with all the Fails), and his Software B would be a Fail.  and Both of Mary's would be a Pass

I was assuming I would set a value for Fail, and then change it to a different value if it encounters a Pass.  but then it needs to reset per user name and software title.

UserName	Software	Month	Pass/Fail
John	SoftwareA	Jan	Fail
John	SoftwareA	Feb	Fail
John	SoftwareA	Mar	Fail
John	SoftwareA	Apr	Pass
John	SoftwareB	Feb	Fail
John	SoftwareB	Mar	Fail
Mary	SoftwareA	Jan	Pass
Mary	SoftwareB	Jan	Pass

[ImkeF · Accepted solution]

Hi @rjtop ,
I would group on UserName and Software and then evaluate the MAX of Pass/Fail:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserName", type text}, {"Software ", type text}, {"Month", type text}, {"Pass/Fail", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UserName", "Software "}, {{"Result", each List.Max([#"Pass/Fail"]), type nullable text}, {"Partition", each _, type table [UserName=nullable text, #"Software "=nullable text, Month=nullable text, #"Pass/Fail"=nullable text]}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Month", "Pass/Fail"}, {"Month", "Pass/Fail"})
in
    #"Expanded Partition"

Also check the file enclosed.

[Nathaniel_C · Accepted solution]

Hi @rjtop ,
 @ImkeF gives an elegant solution in Power Query, which is the forum that we are in, so that is appropriate. 

I was just thinking about how I might do this as a measure.

Result =
VAR _userName =
    MAX ( TablePF[UserName] ) 
VAR _software =
    MAX ( TablePF[Software ] )
VAR _month =
    MAX ( TablePF[Month] )
VAR _calc =
    CALCULATE ( //Calc value for each user and software
        MAX ( TablePF[Pass/Fail] ),
        FILTER (
            ALL ( TablePF ),
            TablePF[UserName] = _userName
                && TablePF[Software ] = _software
        )
    )
VAR _calcMonth = //Calc minumum month
    CALCULATE (
        MIN ( TablePF[Month] ),
        FILTER (
            ALL ( TablePF ),
            TablePF[UserName] = _userName
                && TablePF[Software ] = _software
        )
    )
RETURN
    IF ( _month = _calcMonth, IF ( _calc = "Pass", "Pass", "Fail" ), "" ) //Only if this month equals min month insert value

 or sorted like this, would show that John SoftwareA is Pass and so forth.

I also thought that this might be good only showing one value for each set. This only puts the value on the row which contains the smallest alpha value for month.


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[rjtop]

While the first person to reply may have been a viable solution, this one was more my within my skillset and I figured it out first with my realworld data.  Thank you all!

[Nathaniel_C]

Hi @rjtop @ImkeF ,
Thank you! 
I marked Imke's solution, so that other's may find it as well.

Nathaniel

[ImkeF · Accepted solution]

Hi @rjtop ,
I would group on UserName and Software and then evaluate the MAX of Pass/Fail:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserName", type text}, {"Software ", type text}, {"Month", type text}, {"Pass/Fail", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UserName", "Software "}, {{"Result", each List.Max([#"Pass/Fail"]), type nullable text}, {"Partition", each _, type table [UserName=nullable text, #"Software "=nullable text, Month=nullable text, #"Pass/Fail"=nullable text]}}),
    #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Month", "Pass/Fail"}, {"Month", "Pass/Fail"})
in
    #"Expanded Partition"

Also check the file enclosed.