Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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 |
Solved! Go to 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:
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_ 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 |
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |