Dynamic ID list from Slicer Selection to Create New Measures/Table

[ylin88_waters]

Hi,

I have three tables:

Table 1: Product (for Slicer)

Product

Product1

Product2

Product3

Table 2: Customer Engagement Action (1 - action needed; 0 - no action needed)

CustomerID  Product1  Product2   Product3

111	1	0	0
222	0	1	1
333	1	0	1
444	0	1	0

Table 3: Sales

CustomerID   Product  Sales    SalesPrediction

111	Product1	11000	1100
111	Product2	12000	1200
111	Product3	13000	1300
222	Product1	21000	2100
222	Product2	22000	2200
222	Product3	23000	2300
333	Product1	31000	3100
333	Product2	32000	3200
333	Product3	33000	3300
444	Product1	41000	4100
444	Product2	42000	4200
444	Product3	43000	4300

I'm trying to create a dashboard so users can do following:

1) Select a produc/products from a slicer (table 1)

2) Generate a list of unique Customers ID from table 2 with any actions needed on the selected products
For example, if Products 1 & 3 are selected,then the customers with actions for Products 1 & 3 are 111, 222,333

3) Go to Table 3 to find these customers (i.e., 111,222,333) and calculate their sum of Sales and Sales Predictions for selected products (products 1 & 3).

I will need this subset to create visuals like a scatter plot with Sales and SalesPrediction as X and Y axes, and customerID as Details. Or a bar chart for sales and sales prediction, group by product.

It will be the best if I can add a column to Table 3 with a dynamic flag for the rows I need - for example flag=1 for those rows with IDs and the products selected (in this example flag=0 for every row with product2 or customerID=444). But it seems PowerBI/DAX can't add a dynamic column for slicer selections. I think what I need are two measures on Table 3, the sum of Sales and the sum of SalesPredictions for IDs 111, 222, 333 on products 1 & 3. Because the filter will automatically pick up products 1 & 3, the key may be  how to get the list of IDs.

Not sure how to write this in DAX, but may be something like following:

Sales by Action =
VAR CustomerID =
Values(CustomerID)
IF(
SELECTEDVALUE('Product'[Product]) = "Product1" && Engagement[Product1] =1 ||
SELECTEDVALUE('Product'[Product]) = "Product2" && Engagement[Product2] =1 ||
SELECTEDVALUE('Product'[Product]) = "Product3" && Engagement[Product3] =1
)
RETURN
SUMX (
CustomerID,
CALCULATE ( SUM(Sales[Sales] ) )
)
// should be able to group by product also, don't know how to put this in DAX

Then do another one for Sales Prediction.

Is it possible to make tihs idea work using measure or it should be a new table? How to write DAX for this?

Thanks in advance.

[ylin88_waters]

Finally got it work using the following model.

The key is on the relationship of customerID between CustomerAct and Customer tables, I had to put cross filter direction as "both", so the Sales table will be filtered with the selected CustomerID.

Ibendlin - thanks a lot for your help!

[lbendlin]

You will want to make a couple of changes. First,  create a Customer table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VIrViVYyMjIC08bGxmDaxMREKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t])
in
    Source

Then unpivot the Engagement table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0VNJRAmEDMI7ViVYyMjKC8iFyIDFjY2MkdRAxExMTJHVAvbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, Product1 = _t, Product2 = _t, Product3 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"CustomerID"}, "Product", "Action"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Action", Int64.Type}})
in
    #"Changed Type"

We can leave the Sales table as is for now but IRL you want to separate the actuals from the forecast.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc0xCsNAEEPRu2ztYkfSQdIbV04fCMn9g4JcrJlqBfPZt++jqsY2Hu/X83t+PKvmnHnHsd0K+IIU6Ar6whRMAWBVEAXVFZ6IAnSFFUTBpZBcFUZhdYW/YxSiKzwZhZciaVUURdUVVhRF6AoriqK/cvwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, Product = _t, Sales = _t, #"Sales Prediction" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sales", Currency.Type}, {"Sales Prediction", Currency.Type}})
in
    #"Changed Type"

Next, wire them together

This will give you most of what you wanted. Adjust relationships and create measures for the rest. Worst case you can combine the Sales and Engagement tables into one (not recommended though).

[ylin88_waters]

Thanks for the reply. This is helpful!

I tried to use this idea and build a similar model. The problem is: when I put Product as a slicer and made a selection (one or two), the Sales table and Engagement table are filtered seperately. But the Sales table still has all customers. What I need is, after user selects one or two products and Engagement table has only N customers left, only Sales from these customers will be used in PBI visuals.

Suppose users pick up Product1, and we have only customers 111 and 222 with engagement action for Product1. We can create a virtual table for this list of customers (111,222). Now, the question is, can we use this virtual table (from Engagement table) as a filter for Sales table to exclude the customers we don't need (no action for selected products)?

[lbendlin]

Here's another model that you can consider (combining the fact tables). It's not a good model but it is better suited to your questions.

[ylin88_waters]

Thanks for the model suggestion. I tried it but found that I still have problem - the filter on Product will be applied to both CustomerID and Sales.
What I need is:

1) Select a Product (or products)
2) Find all CustomerIDs with the action needed for selected Product(s)
3) For this list of customers, get their sales and sales forecasts on ALL Products.

Suppose we have following data:

CustomerID	Product	Action
1	A	1
1	B	0
1	C	0
2	A	1
2	B	1
2	C	0
3	A	0
3	B	0
3	C	1

Here if I select Product A (or A&B), only CustomerIDs = 1 & 2 need Action. I'd like to plot Sales, SalesForecasts data for these two customers for ALL three Products A, B and C.
So in a visual with Sales (x-axis), SalesForecast(y-axis) and CustomerIDs (legend), CustomerID is filtered by Product on Action needed, then we want to know their sales and forecasts
on ALL products.

If we can add a measure on Action_sum by adding the sum of all Action score (either 0 or 1) for each customer, then we could add Action_sum >=1 as a filter to the page to get all CustomerIDs. But I think in Power BI, you can only do this as a column, which is not dynamic. My understanding is we can't add a measure (dynamic) like following:

CustomerID Action_Sum (this sum could change to ZERO depending on product selection)
1                 1
2                 2
3                 1

With this Action_sum we will be able to pick up customers we need (filtering by Action_sum>=1) and go to another Sales table to get their Sales data on all products.

[lbendlin · Accepted solution]

"

What I need is:

1) Select a Product (or products)
2) Find all CustomerIDs with the action needed for selected Product(s)
3) For this list of customers, get their sales and sales forecasts on ALL Products."

Great - that is possible but it requires quite some work.  You need to create a clone of the products table, keep the clone disconnected from the data model and feed the slicer from it.  Then you can use measures to sense the selections of the slicer and do the pointer math (a multi step process). You can use measures as page level filters I think. Or visual level  - don't remember.