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.
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.
Super User"
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.