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
Ria
Frequent Visitor

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
1 ACCEPTED SOLUTION

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.

View solution in original post

2 REPLIES 2
Eric_Zhang
Employee
Employee

@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]))
)

fsdsadsa.png

 

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.

 

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.

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.