Conditional column based on data from different rows and same user

[Arnaudd]

Hello ,

I have the date as in the below power BI table,

I want to  modify column [PassY/N] or create a new conditional column, based on the below cretaia:

if there are two rows rows for the User (ex user A) ,and in one row the column pass(Y/N) ,there is Yes, and in the other row there is No, so in the new column the value is Yes.,  so the output appears as below:

thanks

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[LeandroDeodato · Accepted solution]

You can use the @lbendlin solution, or, if you prefer in dax, can use this mensure.

best regards.

[Arnaudd]

Thanks @lbendlin 

Thanks @LeandroDeodato , the DAX might be easier to implement, I tested it and it works!

but, if I have anather coloumn (ex: number of test 2), and want to do the same aggregation as the one I have done to  : "number of test" , what is the correct DAX fonction to use ?

I added an extra line (Pass2) of the other coloumn but I am getting error : The syntax for '"Pass 2"' is incorrect. 

New_Table = SUMMARIZE('exam (Questions)',
'exam (Questions)'[username],
"Number of Tests", COUNT('exam (Questions)'[ID]),
"Pass", if(CONTAINS('exam (Questions)','exam (Questions)'[Pass(Y/N)],"Yes"),"Yes","No")
"Pass2", if(CONTAINS('exam (Questions)','exam (Questions)'[Pass(Y/N)2],"Yes"),"Yes","No")

)

Thanks

[muhammad_786_1]

There's only 1 comma missing between "Pass" and "Pass2" lines, which causes a syntax issue.

It should be like this:

New_Table = SUMMARIZE('exam (Questions)',
'exam (Questions)'[username],
"Number of Tests", COUNT('exam (Questions)'[ID]),
"Pass", if(CONTAINS('exam (Questions)','exam (Questions)'[Pass(Y/N)],"Yes"),"Yes","No"),
"Pass2", if(CONTAINS('exam (Questions)','exam (Questions)'[Pass(Y/N)2],"Yes"),"Yes","No")
)

Best Regards,
Muhammad Yousaf

LinkedIn

[lbendlin]

Group your data by user.  Add one aggregation for "Count of rows"  and another aggregation for "Maximum of [pass(Y/N)]"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYr98pVidaCUnVK4zlBuZWgzmO6LxQcqNkPguSNpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [user = _t, #"number of test" = _t, #"pass(Y/N)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number of test", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"user"}, {{"Total number of test", each Table.RowCount(_), Int64.Type}, {"pass(Y/N)", each List.Max([#"pass(Y/N)"]), type nullable text}})
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.