Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
PBI_Rookie20
New Member

Dynamic and Encompassing Slicer

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 NameSale In ProgressSale Cost
Sale A1/10/2025100
Sale A1/11/2025100
Sale A1/12/2025100
Sale A1/13/2025100
Sale B1/11/202520
Sale B1/12/202520
Sale C1/12/202550
Sale C1/13/202550
Sale C1/14/202550

  Desired Output Example 1

Slicer Selection: Sale A 
  
Sale In ProgressSale Cost
1/10/2025 
Sale A100
Total 1/10/2025100
  
1/11/2025 
Sale A100
Sale B20
Total 1/11/2025120
  
1/12/2025 
Sale A100
Sale B20
Sale C50
Total 1/12/2025170
  
1/13/2025 
Sale A100
Sale C50
Total 1/13/2025150


Desired Output Example 2

Slicer Selection: Sale C 
  
Sale In ProgressSale Cost
1/12/2025 
Sale A100
Sale B20
Sale C50
Total 1/12/2025170
  
1/13/2025 
Sale A100
Sale C50
Total 1/12/2025150
  
1/14/2025 
  
Sale C50
Total 1/14/202550
1 ACCEPTED SOLUTION
vicky_
Super User
Super User

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:

vicky__1-1738705071501.png

 

View solution in original post

3 REPLIES 3
vicky_
Super User
Super User

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:

vicky__1-1738705071501.png

 

@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 NameSale In ProgressSale CostCountry
Sale A1/10/2025100USA
Sale A1/11/2025100USA
Sale A1/12/2025100USA
Sale A1/13/2025100USA
Sale B1/11/202520Canada
Sale B1/12/202520Canada
Sale C1/12/202550USA
Sale C1/13/202550USA
Sale C1/14/202550USA

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.