Forum Discussion
Dynamic ID list from Slicer Selection to Create New Measures/Table
"
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.
Super UserYou 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).
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.
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 1With 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.