I have two (2) tables: Invoices and Products. They look like this:
Invoices
InvoiceID
SalesRep
Products
InvoiceID
ProductID
Cost
ProductName
The Invoices.InvoiceID column is unique. Any invoice may have 1-n products attached to it. In the Products table, there can be any number of duplicate InvoiceIDs, each showing one ProductID (and its cost) that appeared on that Invoice.
Our business sells licenses to use our products (not the products themselves; just licenses). Those licenses can be renewed yearly. So in our DB, our products have names like 'Product A' and 'Product A (Renewal)'. The '(Renewal)' bit indicates that this product is actually a renewal of Product A, not the first purchase of the license. Apart from that, they are identical.
We need to split our invoices and their products by Renewals and non-renewals (we call it New). In the output table, we want just one row for each invoice, no matter how many products it has. It'll look like this:
|Invoice ID|Sales Rep|Total Cost|Renewal Products |Renewal Cost|New Products |New Cost|
|001 |F. Smith | $100|Product A (Renewal)| $100 | | |
|002 |J. Blow | $250|Product B (Renewal)| $150 |Product A | $100|
|003 |B. Bloggs| $300| | |Product C & Product D| $300|
And so on. Note that where there are multiple products of either New or Renewal, they are concatenated. I achieved that by the DAX:
New Products = CONCATENATEX (filter(Products,
Products[InvoiceID]=min(Invoices[InvoiceID]) && iserror(SEARCH("renewal",
Products[ProductName]))),
Products[ProductName],
" & " ,
Products[ProductName],
ASC)and I did a similar one for the Renewal products. The costs, too, add together, so that if there are multiple products of either New or Renewal, their costs add together, like for invoice 003 in the table above. Again, DAX:
New Total = SUMX(FILTER(Products,
Products[Invoice ID]=min(Invoices[InvoiceID]) && iserror(SEARCH("renewal", Products[ProductName]))),
Products[Cost])So far, so good. I have the table looking the way I want it. But the business wants to be able to slice the output table by New or Renewal. When they choose either, then it as if the other type of business doesn't even exist. The columns for that type of business in the output table will be empty, and the Total Cost column will show only the total cost of any items of the chosen type in the order. And if any invoice has only products of the type not chosen, that invoice will disappear from the output completely.
I am really struggling with this slicer, so any suggestions will be gratefully received. Thanks a lot.
