Need to convert the excel formula into Power BI for categorizing data.

[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" on b3 and it has also value "Requested info from Client"on b6 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 worksheet to work upon, kindly assist with these if its possible on excel.

Excel Formula: =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"),"")

Need to convert the above Formula for PowerBI and want the output column C as shown in image. is it possible ? 

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

You can try this DAX formula as a calculated column.

Column =
VAR hasResolution =
    LOOKUPVALUE (
        Table187[Interaction],
        Table187[TicketID], [TicketID],
        Table187[Interaction], "Resolution"
    )
VAR hasResolved =
    LOOKUPVALUE (
        Table187[Interaction],
        Table187[TicketID], [TicketID],
        Table187[Interaction], "Resolved"
    )
VAR hasRIFC =
    LOOKUPVALUE (
        Table187[Interaction],
        Table187[TicketID], [TicketID],
        Table187[Interaction], "Requested info from Client"
    )
RETURN
    IF (
        NOT ( ISBLANK ( hasResolution ) && ISBLANK ( hasResolved ) )
            && NOT ISBLANK ( hasRIFC ),
        "OurSide",
        "ClientSide"
    )

Best Regards!

Dale

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula in the PowerPivot window

=if(AND(CALCULATE(COUNTROWS(Data),FILTER(Data,(Data[Interaction]="Resolution"||Data[Interaction]="Resolved")&&Data[TicketID]=EARLIER(Data[TicketID])))>0,CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Interaction]="Requested info from client"&&Data[TicketID]=EARLIER(Data[TicketID])))>0)=TRUE(),"Our side","Client side")

You may download the workbook from here.  As you can observe in the image below, this will show client side/our side in all cells of that column.  Can you live with this?

[Anonymous]

Thank you very much for this, this too worked out as expected.

[Ashish_Mathur]

You are welcome.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

You can try this DAX formula as a calculated column.

Column =
VAR hasResolution =
    LOOKUPVALUE (
        Table187[Interaction],
        Table187[TicketID], [TicketID],
        Table187[Interaction], "Resolution"
    )
VAR hasResolved =
    LOOKUPVALUE (
        Table187[Interaction],
        Table187[TicketID], [TicketID],
        Table187[Interaction], "Resolved"
    )
VAR hasRIFC =
    LOOKUPVALUE (
        Table187[Interaction],
        Table187[TicketID], [TicketID],
        Table187[Interaction], "Requested info from Client"
    )
RETURN
    IF (
        NOT ( ISBLANK ( hasResolution ) && ISBLANK ( hasResolved ) )
            && NOT ISBLANK ( hasRIFC ),
        "OurSide",
        "ClientSide"
    )

Best Regards!

Dale

[Anonymous]

Thank you very much for this, it worked as expected.