Forum Discussion
Create a filtered table with an if statement
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.
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.
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.