Slicing two visuals uses values from one of them

[jmkvalsund]

I have three tables:

• "Customers" containing customerID and name etc.
• "LongTermDeals" (Ltd) containing long term deals with numerous records for each deal, typically data added every day with new dateid
• "ShortTermDeals" (Std) containg running deals, without history, only current value for each deal.
• Both std and ltd containing customerID and related by a one-to-many from CustomerID 

Putting both in table-visuals and adding customerID from Customers, I can click on a customer in either of them and get the same customers record in the other. Fine!

But; I want to add a slicer based on the values inLtd, and then "slice " the actual customers in both tables, not only in Ltd as I do by now. How can I relate Ltd to Std (and the opposite) to use slicer values from both tables to get the common customers?

[johnt75]

you could create a new dimension table, something like

All values =
DISTINCT (
    UNION (
        ALLNOBLANKROW ( 'long term deals'[values] ),
        ALLNOBLANKROW ( 'short term deals'[values] )
    )
)

and then link that to both tables and use it in slicers and visuals.

[jmkvalsund]

Thanks, that works in the case where I slice on the values of the deals.

Then on Std I also want to slice on the product name, (so that selecting a product in Std also shows the Ltd's for the customers having both), how do I create the dimension table in that case? It will need two columns, value and productname, where productname is filled with NULL's from Ltd but I don't see how I can create that table using ALLNOBLANKROW..

[johnt75 · Accepted solution]

In that case I would probably look to Power Query rather than DAX. Reference the query which pulls std, remove the columns you're not interested in and then remove duplicates. do the same for ltd and merge the 2 queries. you will then be able to link that new table to both std and ltd

[johnt75]

Create a slicer from the Customers table and add a filter to it such that the customer ID from Ltd is not blank.

[jmkvalsund]

Ok, bad explaining from my side, I want to slice based on the values of the deals, not on the customerID's. And when slicing on the values of the Ltd's, I want the Std's to be sliced as well.