cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Create User Defined Bin Slicer with Parameter

I have some data that looks like this:

 Employee ID Age (years) 1 100 2 1 3 18 4 21 5 65 6 7 7 12 8 18 9 24 10 36

I am trying to create a histogram that shows this data, with a slicer that will allow my users to define the size of the bins that are present in the visual.

There is a blog post that many people have pointed to that solves this problem. I am trying to follow this method exactly, but the slicer it creates is not interacting with my visual. Does this method still work after the recent updates to the 'New Parameter' Menu?:

2 ACCEPTED SOLUTIONS
Solution Sage

With the new New Parameters menu, select Numeric Range.  It works with the link you supplied. (pbix: 2 - Choose SIZE of Bins.pbix)

Let me know if you have any questions.

pbix: prattja9.pbix

Solution Sage

Does this help?  I changed  to COUNTROWS( DimCustomer ) to

DISTINCTCOUNT( 'DimCustomer'[Employee ID] ) in the RETURN.

Count of Customers in each Band - by size - 2 =
VAR _minAge = [Min Age]
VAR _maxAge = [Max Age]
VAR _bandSize = [What is Band Size Value]
VAR _AgeBandTable =
GENERATESERIES(
_minAge,
_maxAge,
_bandSize
)
VAR _currAge = SELECTEDVALUE( 'Age Band'[Age] )
MAXX(
FILTER(
_AgeBandTable,
[Value] <= _currAge
),
[Value]
)
VAR _bandTail = _bandHead + _bandSize
RETURN
IF(
CALCULATE(
DISTINCTCOUNT( 'DimCustomer'[Employee ID] ),
&& 'Age Band'[Age] < _bandTail
)
)

pbix: prattja9.pbix

5 REPLIES 5
Solution Sage

With the new New Parameters menu, select Numeric Range.  It works with the link you supplied. (pbix: 2 - Choose SIZE of Bins.pbix)

Let me know if you have any questions.

pbix: prattja9.pbix

Frequent Visitor

Interesting, I got it to work with the sample BI report. Another question, my actual data set has multiple line items for each employee. Is there a way to modify the 15th line in the "Count of Customers in each Band - by size" measure to accomodate that complication?

Solution Sage

Does this help?  I changed  to COUNTROWS( DimCustomer ) to

DISTINCTCOUNT( 'DimCustomer'[Employee ID] ) in the RETURN.

Count of Customers in each Band - by size - 2 =
VAR _minAge = [Min Age]
VAR _maxAge = [Max Age]
VAR _bandSize = [What is Band Size Value]
VAR _AgeBandTable =
GENERATESERIES(
_minAge,
_maxAge,
_bandSize
)
VAR _currAge = SELECTEDVALUE( 'Age Band'[Age] )
MAXX(
FILTER(
_AgeBandTable,
[Value] <= _currAge
),
[Value]
)
VAR _bandTail = _bandHead + _bandSize
RETURN
IF(
CALCULATE(
DISTINCTCOUNT( 'DimCustomer'[Employee ID] ),
&& 'Age Band'[Age] < _bandTail
)
)

pbix: prattja9.pbix

Frequent Visitor

Got it to work using CALCULATE(DISTINCTCOUNT( instead of CALCULATE(COUTROWS(.

Solution Sage

Sorry.  I didn't see this post until I posted.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

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

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors