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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LazyUser
Helper I
Helper I

Need help in making a disconnected slicer work

Please think of a simple sales data model with appropriate facts and dimensions ( I can share a dummy model if needed). 
Dimensions include a Products table, Date table, Stakeholders table, and Salesperson table having information about Salesperson, their city, facility, and Organization. 
Facts is a Sales table with foreign keys and is a star schema. 

Client wants slicers such that if they select a number between 2 and 10, my report should show data of those 'Organizations' which made sales between 2 and 10M$ in selected time period. 

For that, I created a disconnected What-If parameter table using generate series. 

New measure =
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
IF([Total sales] > _min && [Total sales] < _max, 1 , 0 )

I have a Sales by Org chart and in that chart. I added this DAX measure to visual level filter of this chart and it works fine. The data is restricted and the numbers match. If I select a row from this chart, it will filter out other visuals KPIs, Profits by Products etc. But the client wants this functionality without having to manually select a row in my Sales by Org chart. 

Please guide or suggest workarounds. 

1 ACCEPTED SOLUTION

@LazyUser , all you measures to created like this or filter this in visual level filter for non blank

 

New measure =
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
IF([Total sales] > _min && [Total sales] < _max, 1 , blank()  )

 

 

example of measures needed in table

New sales=
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
IF([Total sales] > _min && [Total sales] < _max, [Total sales] , blank()  )

 

 

count of organizations

 

New measure =
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
sumx(values(Table[Organization]) ,IF([Total sales] > _min && [Total sales] < _max, 1 , blank()  ))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

4 REPLIES 4
LazyUser
Helper I
Helper I

Thanks @amitchandak , this simple DAX worked!

amitchandak
Super User
Super User

@LazyUser , Use what if parameter or generateseries of table and try  measures like

 

Total sales = SUM(Sales)

 

then create a new measure

 

New measure =
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
IF([Total sales] > _min && [Total sales] < _max, 1 , 0 )

 

https://docs.microsoft.com/en-us/power-bi/desktop-what-if

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi @amitchandak I did the same and used this measure in a chart which shows Sales by Organization and I got the right result. 
If I select all the Organizations from this widget, then every other widget (KPIs, Widgets using other dimensions such as Products) on the dashboard gets filtered out. 

Client wants this latter functionaity too without having to manually select the organization from the filtered chart. 

Edit: I get it why it was not clear from my initial question, editing it now. Sorry for the confusion!

@LazyUser , all you measures to created like this or filter this in visual level filter for non blank

 

New measure =
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
IF([Total sales] > _min && [Total sales] < _max, 1 , blank()  )

 

 

example of measures needed in table

New sales=
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
IF([Total sales] > _min && [Total sales] < _max, [Total sales] , blank()  )

 

 

count of organizations

 

New measure =
var _max = maxx(allselected(whatif),whatif[value])
var _min = maxx(allselected(whatif),whatif[value])
return
sumx(values(Table[Organization]) ,IF([Total sales] > _min && [Total sales] < _max, 1 , blank()  ))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.