Slicer for Merged, Multi-Column Rows

Question

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

Edit

Alexis, if the original table looks 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|

Then after slicing to show only New, it would look like this:

|Invoice ID|Sales Rep|Total Cost|Renewal Products   |Renewal Cost|New Products         |New Cost|
|002       |J. Blow  |      $100|                   |            |Product A            |    $100|
|003       |B. Bloggs|      $300|                   |            |Product C & Product D|    $300|

And after slicing to show only Renewal, it would 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  |      $150|Product B (Renewal)|       $150 |                     |        |

Does that help?

Can you show how you want the table to look after slicing and how it currently looks after slicing? — Alexis Olson

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-23T16:39:03

This is actually simpler than you might expect. All you need to do is create a calculated column on the Products table that distinguishes between the new and renewal rows:

Slicer = IF(SEARCH("Renewal", Products[ProductName], 1, 0) > 0, "Renewal", "New")

Then use this column as a slicer and the table should behave as you specified.

Note: If you create this column first, then your measures can reference it rather than using the SEARCH function. I.E., you can replace

`iserror(SEARCH("renewal", Products[ProductName]))`

with

Products[Slicer] = "New"

Slicer for Merged, Multi-Column Rows

1 Answers