Need to create a function to Categorize values

[Anonymous]

Is there any way to create a column which will display only two values i.e "ClientSide", "OurSide" based on Colum A(ticketid) and ColumnB(interactionValue.)

-For Example: if ticketId(ColumnA) 5733199 has Interaction(columnB) as "Resolution" or "Resolved" and it has also value "Requested info from Client" then i want a column stating a value "ourSide" , if not the value should state "ClientSide" but it should check all the related row in InteractionValue Column for ticketId 5733199.

-This loop should run till the end of Column A(TicketId) and give result Accordingly.

here is an example in excel i want this formula converter for powerBI

Formula for Excel: =IF(A2<>A1,IF(AND(COUNTIFS(A:A,A2,B:B,"Resolution")+COUNTIFS(A:A,A2,B:B,"Resolved")>0,COUNTIFS(A:A,A2,B:B,"Requested info from Client")>0),"OurSide","ClientSide"),"")

[MarkS]

Hi @Anonymous,

Here is a way using Query Editor, enter your table name in the Source step.

let
    Source = Table,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TicketID", Int64.Type}, {"Interaction", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TicketID"}, {{"TempTable", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each List.ContainsAny([TempTable][Interaction],{"Resolution","Resolved"})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Contains([TempTable][Interaction],"Requested Info from Client")),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Custom.2", each if ([Custom]  and [Custom.1]) then "OurSide" else "ClientSide"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Custom", "Custom.1"}),
    #"Expanded TempTable" = Table.ExpandTableColumn(#"Removed Columns", "TempTable", {"TicketID", "Interaction"}, {"TicketID.1", "Interaction"})
in
    #"Expanded TempTable"

[zayan]

To create a conditional column, select the Data view in Power BI Desktop from the left side of the report canvas.

Select the the table that you need to add a column.

From the Add column tab (top in the ribbon), select Conditional Column.

This will popup.

Add your conditions and rules, and giva acolumn name instead of custom.

Enjoy!

[Rfranca]

Hi, @Anonymous

Please, share the link where I can download your data file to help with the solution.

Best Regards,
Rfranca

[Rfranca]

hi, @Anonymous, @MarkS, @zayan

When you use the POWERBI the functions are similar because the concept is different. In Excel you work with cells to calculate calculations and in POWREBI you work with tables.

With this the functions in the POWERBI must be written attending the concept of tables.

Come on....

1. Import your data with the TICKETID and INTERATION column;
2. Create a [QtResolution] measure to count tickets with interaction equal to Resolution;

QtResolution = CALCULATE(COUNTROWS(DADOS);FILTER(ALL(DADOS[Interaction]);DADOS[Interaction]="Resolution"))

3. Create a [QtResolved] measure to count tickets with interaction equal to Resolved;

QtResolved = CALCULATE(COUNTROWS(DADOS);FILTER(ALL(DADOS[Interaction]);DADOS[Interaction]="Resolved"))

4. Create a [QtInfoClient] measure to count tickets with interaction equal to Requested Info from Client;

QtInfoClient = CALCULATE(COUNTROWS(DADOS);FILTER(ALL(DADOS[Interaction]);DADOS[Interaction]="Requested Info from Client"))

5. Create a [StatusInteration] measure to set the ticket STATUS.

StatusInteration = if(
 and(
 ([QtResolution]+[QtResolved])>0;[QtInfoClient]>0
 );"OurSide";"ClientSide"
 )

Attention with the texts of the interactions (Resolution, Resolved, Requested Info from Client) if it changes you must update the measures.

Here's how it was on the table.

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

[Rfranca · Accepted solution]

hi, @Anonymous

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca