Dynamic and Encompassing Slicer

[PBI_Rookie20]

Hi, I am in need of assistance creating a dynamic slicer where a single Sale Name selection returns all other sales while the selected sale is in progress.  Here is an example of the raw data and desired output in PBI.  I'm assuming this is a DAX but I can't figure that part out. Thank you so much! 

Raw Data
Sale Name	Sale In Progress	Sale Cost
Sale A	1/10/2025	100
Sale A	1/11/2025	100
Sale A	1/12/2025	100
Sale A	1/13/2025	100
Sale B	1/11/2025	20
Sale B	1/12/2025	20
Sale C	1/12/2025	50
Sale C	1/13/2025	50
Sale C	1/14/2025	50

  Desired Output Example 1

Slicer Selection: Sale A
Sale In Progress	Sale Cost
1/10/2025
Sale A	100
Total 1/10/2025	100
1/11/2025
Sale A	100
Sale B	20
Total 1/11/2025	120
1/12/2025
Sale A	100
Sale B	20
Sale C	50
Total 1/12/2025	170
1/13/2025
Sale A	100
Sale C	50
Total 1/13/2025	150

Desired Output Example 2

Slicer Selection: Sale C
Sale In Progress	Sale Cost
1/12/2025
Sale A	100
Sale B	20
Sale C	50
Total 1/12/2025	170
1/13/2025
Sale A	100
Sale C	50
Total 1/12/2025	150
1/14/2025
Sale C	50
Total 1/14/2025	50

[vicky_ · Accepted solution]

I created a new table with just the values of the Sales Names, and left it disconnected.

Sale Names = VALUES('Table'[Sale Name])

Then created the measure below:

Sale Cost (Measure) = 
var saleInProgressDates = CALCULATETABLE(VALUES('Table'[Sale In Progress]), TREATAS(VALUES('Sale Names'[Sale Name]), 'Table'[Sale Name]))
RETURN CALCULATE(SUM('Table'[Sale Cost]), TREATAS(saleInProgressDates, 'Table'[Sale In Progress]))

'Table' is the raw data you've provided, and  'Sale Names' is the table created above.

Result below:

[PBI_Rookie20]

@vicky_  This is amazing! Thank you so much! Upon transposing this example to the actual dataset, the measure is collapsing one additional field which I've marked country in the raw data table.  For example, selecting Sale A or C will hide Canada from the Matrix.  Can the measure be adjusted to account for this or is this a limitation of PBI? Thank you again!

Sale Name	Sale In Progress	Sale Cost	Country
Sale A	1/10/2025	100	USA
Sale A	1/11/2025	100	USA
Sale A	1/12/2025	100	USA
Sale A	1/13/2025	100	USA
Sale B	1/11/2025	20	Canada
Sale B	1/12/2025	20	Canada
Sale C	1/12/2025	50	USA
Sale C	1/13/2025	50	USA
Sale C	1/14/2025	50	USA

[vicky_]

Hi, 
will there always be a 1:1 relationship between the sale name and country? You can basically re-use the above measure, but change SUM('Table'[Sale Cost]) to MAX('Table'[Country]). Otherwise, you can place the country dimension under Sale Name in the heirarchy, and turn of the row sub-totals so that it looks a little cleaner.