Filter clients on product slicer then orders pertaining to clients

[CMunteanu]

In the following scenario of a dimensional star model:

Customer Dimension:

CUSTOMER	CUSTOMER_ID
Joe	C1
Peter	C2
Pierre	C3
Paul	C4
Anton	C5

Product Dimension:

PRODUCT	PRODUCT_ID
APPLE	P1
ORANGE	P2
BANANA	P3
STRAWBERRY	P4

Order Fact Table:

ORDER	CUSTOMER_ID	PRODUCT_ID	DATE	AMOUNT
O1	C1	P3	3-11-2019	234
O2	C1	P3	5-12-2018	12
O3	C3	P2	8-2-2009	123
O4	C3	P3	12-12-2021	892
O5	C4	P4	21-10-2020	222
O6	C4	P4	13-7-2021	45
O7	C4	P3	8-8-2009	125
O8	C5	P1	9-12-2011	212
O9	C5	P2	6-7-2005	672
O10	C5	P3	7-3-2003	34

The star model use relationships:

Customer.CUSTOMER_ID --> Order.CUSTOMER_ID
Product.PRODUCT_ID --> Order.PRODUCT_ID

We need a USER SLICER to select a product. When user selects a product, first we need to filter the customers who have ordered the selected product, and then return all orders for these clients.

For example, if user selects "ORANGE" the customers filtered should be Pierre (C3) and Anton (C5) (the model filtered 2 Customers), and the final resultset should be all orders of these customers (O3, 04, 08, 09, 010). Afterwards, we could create metrics on the filtered scenario.

How can I implement the dynamic model with this user product slicer in PowerBI Report to filter the customers (and theirs orders) who have ordered a selected product?

[johnt75 · Accepted solution]

You could create a measure to get the total sales value of the orders like

Sales to customers of chosen product =
VAR FilteredCustomers =
    VALUES ( 'Orders'[Customer ID] )
VAR FilteredOrders =
    CALCULATETABLE (
        VALUES ( 'Orders'[Order ID] ),
        REMOVEFILTERS ( 'Product' ),
        FilteredCustomers
    )
VAR FilteredSales =
    CALCULATE (
        SUM ( 'Orders'[Amount] ),
        REMOVEFILTERS ( 'Product' ),
        FilteredOrders
    )
RETURN
    FilteredSales

To get different metrics, like the number of orders, you would need to replace the SUM in the final calculate with whatever measure you wanted.

If you want to show the orders in a visual then you could create a measure like

Order is visible =
VAR CurrentOrder =
    SELECTEDVALUE ( 'Orders'[Order ID] )
VAR FilteredCustomers =
    VALUES ( 'Orders'[Customer ID] )
VAR FilteredOrders =
    CALCULATETABLE (
        VALUES ( 'Orders'[Order ID] ),
        REMOVEFILTERS (),
        FilteredCustomers
    )
VAR IsVisible = CurrentOrder IN FilteredOrders
RETURN
    IF ( IsVisible, 1 )

and add it as a visual level filter to only show if the value is 1

[v-zhangti · Accepted solution]

Hi, @CMunteanu 

You can try the following methods.

New Table:

Slicer = VALUES('Product Dimension'[PRODUCT])

ORDER = VALUES('Order Fact Table'[ORDER])

Measure:

Measure ORDER = 
Var _Product=SELECTEDVALUE('Slicer'[PRODUCT])
Var _ProductID=CALCULATETABLE(VALUES('Product Dimension'[PRODUCT_ID]),'Product Dimension'[PRODUCT]=_Product)
Var _table1=CALCULATETABLE(VALUES('Order Fact Table'[CUSTOMER_ID]),'Order Fact Table'[PRODUCT_ID]=_ProductID)
Var _table2=CALCULATETABLE(VALUES('Order Fact Table'[ORDER]),'Order Fact Table'[CUSTOMER_ID] in _table1)
Return
IF(SELECTEDVALUE('ORDER'[ORDER]) in _table2,1,0)

Measure CUSTOMER = 
Var _Product=SELECTEDVALUE('Slicer'[PRODUCT])
Var _ProductID=CALCULATETABLE(VALUES('Product Dimension'[PRODUCT_ID]),'Product Dimension'[PRODUCT]=_Product)
Var _table1=CALCULATETABLE(VALUES('Order Fact Table'[CUSTOMER_ID]),'Order Fact Table'[PRODUCT_ID]=_ProductID)
Return
IF(SELECTEDVALUE('Customer Dimension'[CUSTOMER_ID]) in _table1,1,0)

Put Measure in the filter of the view you want to filter and set it to equal 1.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @CMunteanu 

with proper setting of the visual interactions, this can be done without any measure.

here we go:

1) plot a table/matrix visual from the columns all from the fact table

2) create a product slicer with the product[product]

3) create a customer slicer with customer[customer]

4) left click the product slicer, in the ribbon, format/edit interactions, cilck none on the table/matrix visual.

then it shall behave as you expect, like this:

[johnt75 · Accepted solution]

You could create a measure to get the total sales value of the orders like

Sales to customers of chosen product =
VAR FilteredCustomers =
    VALUES ( 'Orders'[Customer ID] )
VAR FilteredOrders =
    CALCULATETABLE (
        VALUES ( 'Orders'[Order ID] ),
        REMOVEFILTERS ( 'Product' ),
        FilteredCustomers
    )
VAR FilteredSales =
    CALCULATE (
        SUM ( 'Orders'[Amount] ),
        REMOVEFILTERS ( 'Product' ),
        FilteredOrders
    )
RETURN
    FilteredSales

To get different metrics, like the number of orders, you would need to replace the SUM in the final calculate with whatever measure you wanted.

If you want to show the orders in a visual then you could create a measure like

Order is visible =
VAR CurrentOrder =
    SELECTEDVALUE ( 'Orders'[Order ID] )
VAR FilteredCustomers =
    VALUES ( 'Orders'[Customer ID] )
VAR FilteredOrders =
    CALCULATETABLE (
        VALUES ( 'Orders'[Order ID] ),
        REMOVEFILTERS (),
        FilteredCustomers
    )
VAR IsVisible = CurrentOrder IN FilteredOrders
RETURN
    IF ( IsVisible, 1 )

and add it as a visual level filter to only show if the value is 1