Forum Discussion

Ria's avatar
Ria
Frequent Visitor
8 years ago
Solved

Create a filtered table with an if statement

I need to account for two exceptions in my Sales data by creating a separate table.  

 

1. When a credit/rebill is done to change the client code of the bill.  This causes the data to have the same bill number show up twice in my data with different customer names.  In this case I need the Max of the created date.  

 

In the second case there may be a separate charge associated with the bill that is billed to a third party.  The Bill number will be the same but will have a "A", "P" or "E" appended.  In this case I need the Min of the created date.

 

In Summary:  Create a table with the client name and work order number which would use the first created date if the last character is either a "A" "P" or "E" or the last created date if the last character is not  "A" "P" or "E"

 

I am able to create the expressions but am  getting lost in putting them together.  This is what I have so far. Not ssure where to put the If statement, and how to add the Client column to the target table.

 

CUSTOMER WO = SUMMARIZE(Sales, Sales[WO NUM],"CREATED DATE",CALCULATE(SUM(Sales[CREATED_TIME]),FILTER(sales,Sales[CREATED_TIME]=max(Sales[CREATED_TIME]))))

 

Original table is:

Sales     
     
Bill Number WO NUMClientCreated TimeLast Charater
C506550C506550XWZ2-Apr-17 
C506550C506550ABC6-Apr-17 
E478923E478923UVW9-May-17 
E478923PE478923FGH12-May-17P
E789278E789278LMN1-May-17 

 

and new table is:

CUSTOMER WO 
   
WO NUMClientCREATED DATE
C506550ABC6-Apr-17
E478923UVW9-May-17
E789278LMN1-May-17
Show and Tell
  • Ria's avatar
    Ria
    8 years ago

    This solution was a good start but the data was more complex than originally anticipated.  More filters were required.  The final solution was to add 2 more columns 

    Min Acct = if(Sales[ACCT_CODE] = CALCULATE(MIN(Sales[ACCT_CODE]),ALLEXCEPT(Sales,Sales[BILL_NUMBER],Sales[DOCUMENT_TYPE])),"yes","no")

     

    CURRENT INV = IF(CALCULATE(MAX(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "REBILL", "REBILL",iF(CALCULATE(MIN(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "CREDIT","CREDIT","INVOICE"))

     

    and then to create the table

     

    Customer WO = SELECTCOLUMNS(filter(  sales,sales[DOCUMENT_TYPE]= Sales[CURRENT INV] && Sales[CREATED_TIME] = Sales[Column] && Sales[Min Acct] = "yes" ), "WO NUMBER", Sales[WO NUM], "CLIENT", Sales[CLIENT],"CREATED TIME", Sales[CREATED_TIME])

     

    This works except for one condition.  When there is an "A", "P" or "E" bill and there is a CREDIT/REBILL invoice.  Only a very small percentage of invoices fall in this category, so the solution is acceptable.

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago

    Ria

    You can add a column in your original table with the below DAX:

    Column  = IF (
    CALCULATE (
        MAX ( sales[last character] ),
        ALLEXCEPT ( sales, sales[wo number] )
    )
        = "P"
        || CALCULATE (
            MAX ( sales[last character] ),
            ALLEXCEPT ( sales, sales[wo number] )
        )
            = "A"
        || CALCULATE (
            MAX ( sales[last character] ),
            ALLEXCEPT ( sales, sales[wo number] )
        )
            = "E",
    CALCULATE(MIN(sales[created time]),ALLEXCEPT(sales,sales[wo number])),
    CALCULATE(MAX(sales[created time]),ALLEXCEPT(sales,sales[wo number]))
)

     

    Then create a calculated table as below

    [Table ] =
SELECTCOLUMNS (
    FILTER ( sales, sales[created time] = sales[column] ),
    "WO NUMBER", sales[wo number],
    "CLIENT", sales[client],
    "CREATED TIME", sales[created time]
)

    See more details in the attached pbix file.

     

    • Ria's avatar
      Ria
      Frequent Visitor
      8 years ago

      This solution was a good start but the data was more complex than originally anticipated.  More filters were required.  The final solution was to add 2 more columns 

      Min Acct = if(Sales[ACCT_CODE] = CALCULATE(MIN(Sales[ACCT_CODE]),ALLEXCEPT(Sales,Sales[BILL_NUMBER],Sales[DOCUMENT_TYPE])),"yes","no")

       

      CURRENT INV = IF(CALCULATE(MAX(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "REBILL", "REBILL",iF(CALCULATE(MIN(Sales[DOCUMENT_TYPE]),ALLEXCEPT(Sales,Sales[WO NUM])) = "CREDIT","CREDIT","INVOICE"))

       

      and then to create the table

       

      Customer WO = SELECTCOLUMNS(filter(  sales,sales[DOCUMENT_TYPE]= Sales[CURRENT INV] && Sales[CREATED_TIME] = Sales[Column] && Sales[Min Acct] = "yes" ), "WO NUMBER", Sales[WO NUM], "CLIENT", Sales[CLIENT],"CREATED TIME", Sales[CREATED_TIME])

       

      This works except for one condition.  When there is an "A", "P" or "E" bill and there is a CREDIT/REBILL invoice.  Only a very small percentage of invoices fall in this category, so the solution is acceptable.