Use slicer to filter and exclude from results

[robbiecutting]

Hi,

Please bear with me while I try to explain the issue as best as possible!

I have a dataset which shows items within orders, with one of the fields denoting the colour of the item. There can be several items, with several different colours, on the same order.

What I am trying to achieve is to allow the user to select the colour in a slicer in the top and the report displays the Qty of all the OTHER colours that particular order may have.

For example, with dataset:

Order #    Colour    Qty

1               Black       1

1               Green      2

1               Red         1

2               Blue         3

2               Black        1

3               Green       2

4              Black        2

Selecting the 'Black' colour from a slicer at the top would result in:

Order #    Colour    Qty

1               Green     2

1               Red         1

2               Blue        3

Basically, I can replicate the above in SQL by doing the following:

• Get list of order numbers containing 'Black' items (list_of_order_nums)
• Get details of orders in the list (order_details)
• Filter details (order_details) to exclude 'Black' items

E.g

SELECT *

FROM fact_orders

WHERE order_num IN(SELECT order_num FROM fact_orders WHERE colour = 'Black' GROUP BY order_num)

AND colour != 'Black'

Is this possible within Power BI?

Thanks

[erik_tarnvik]

OK here is one suggestion:

Create a table that contains just the the colors, this can be done by clicking "New Table" under "Modeling" and define the new table as

Colors = SUMMARIZE(Data,Data[Color])

(this assumes your data is in a table called Data).

Make sure that there are no relationships created between Data and Colors. Create a slicer for Colors.

Now create a measure:

NotOfColor = 
IF(
   NOT(HASONEVALUE(Colors[Color]))
   || NOT(CONTAINS(Data,Data[Color],MAX(Colors[Color]))),
   1,
   0)

In your table with the Data, drag the NotOfColor measure to the Visual Filter and create a filter that shows data only if NotOfColor evaluates to 1.

Now if you select a color in the slicer, your Data table should show only the rows that does not have the color you selected.

[robbiecutting]

Hi @erik_tarnvik,

Thanks for your reply.

If I'm correct I think your solution would return all row which does not have the colour selected. The problem is I only need to see the details of orders which contain the selected colour. So in the example, I wouldn't want to see order #3, as this has no black rows within the order.

So for the example, in summary, the process is:

• Find all orders containing 'Black' items
• Return only those orders containing 'Black' items
• Remove the details of the 'Black' item rows from these orders

Hope that makes sense, and thanks for your suggestion

Thanks

[erik_tarnvik]

Hi @robbiecuttin,

sorry for not reading your question carefully enough, my bad.

Following the lines of my previous solution, you can achieve that by adding yet another measure:

OrderHasColor = 
VAR OrderNumber = MAX(Data[Order])
RETURN
IF(NOT(HASONEVALUE(Colors[Color]))
   || COUNTROWS(FILTER(ALL(Data),Data[Order] = OrderNumber && Data[Color] = MAX(Colors[Color])))  > 0,
   1,
   0)

Then use this measure in the same way as the other one, add it to visual filters and include only lines that evaluate to 1. You could of course combine these two measures into one in order not to clutter your model too much...

[OwenAuger · Accepted solution]

Hi @robbiecutting

I would recommend using Basket Analysis from DAX Patterns with a slight modification.

Here's a pbix file using your sample data.

First set up the data model with a 'Filter Colour' table, with an inactive relationship with the Orders table:

Then define measures as follows:

Quantity =
SUM ( Orders[Qty] )

Quantity from Orders Containing Filter Colour excluding Filter Colour itself =
CALCULATE (
    [Quantity],
    CALCULATETABLE (
        SUMMARIZE ( Orders, Orders[Order #] ),
        ALL ( Orders[Colour] ),
        USERELATIONSHIP ( Orders[Colour], 'Filter Colour'[Filter Colour] )
    ),
    EXCEPT ( VALUES ( Orders[Colour] ), VALUES ( 'Filter Colour'[Filter Colour] ) )
)

Using the second measure in a table gives the desired result:

Regards,

Owen

[nikit]

 Hi @OwenAuger

Sorry for an unrelated question: I've downloaded pbix file you've kindly attached and I'm struggling to understand how you've made a default slicer to display 'buttons' instead of a list?

With the new ability for default slicers to be synced across tabs I would prefer to show users a 'chiclet slicer' view whilst keeping the new functionality of a default slicer.

Kind regards,

Nikita

[OwenAuger]

Hi Nikita,

Sure - To get the button appearance

1. Select the slicer and go to Format->General

2. Change Orientation to Horizontal

3. Turn Responsive On (to allow multiple rows of items)

4. I also set a background colour in Items->Background to get a button-like appearance

Microsoft documentation here:

https://docs.microsoft.com/en-us/power-bi/power-bi-slicer-filter-responsive

Regards,

Owen

[avinash726]

Hi Owen,

I am facing similar kind of challlenge.

I have two tables UserDetails and DateDim as below:

Username     Date

Alan               10-07-2018

Nick                11-07-2018

Neil                 12-07-2018

and

Date

10-07-2018

11-07-2018

12-07-2018

I am looking for a table that gives me the username and date which is not selected in date slicer having value of date from DateDim.

Hope you understood my challenge. thanks!

[robbiecutting]

Hi @OwenAuger,

Thanks for your response, this seems exactly what I was looking for (didn't think of creating a calculated table).

Let me give that a go and I'll let you know how it goes.

Thanks again