Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Need to create a function to Categorize values

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"),"")

e56a24f4-0b48-4a39-89ef-1a93575c05d0 (1).PNG

1 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

View solution in original post

5 REPLIES 5
MarkS
Resolver IV
Resolver IV

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
Regular Visitor

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.

 

Untitled.png

 

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

 

Enjoy!

 

Hi, @Anonymous

 

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

 

Best Regards,
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.

 

Clipboard01.gif

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.