Forum Discussion

Arnaudd's avatar
Arnaudd
New Member
1 year ago
Solved

Conditional column based on data from different rows and same user

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

Need Help
Show and Tell

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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.

  • LeandroDeodato's avatar
    LeandroDeodato
    Resolver II
    1 year ago

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

     

    best regards.

     

     

    • Arnaudd's avatar
      Arnaudd
      New Member
      1 year ago

      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's avatar
        muhammad_786_1
        Solution Supplier
        1 year ago

        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