Forum Discussion

prattja9's avatar
prattja9
Frequent Visitor
3 years ago
Solved

Create User Defined Bin Slicer with Parameter

I have some data that looks like this: 

 

Employee IDAge (years)
1100
21
318
421
565
67
712
818
924
1036

 

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?:
https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-size-of-bins

Need Help
  • grantsamborn's avatar
    grantsamborn
    3 years ago

    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] )
VAR _bandHead =
    MAXX(
        FILTER(
            _AgeBandTable,
            [Value] <= _currAge
        ),
        [Value]
    )
VAR _bandTail = _bandHead + _bandSize
RETURN
    IF(
        _currAge = _bandHead,
        CALCULATE(
            DISTINCTCOUNT( 'DimCustomer'[Employee ID] ),
            'Age Band'[Age] >= _bandHead
                && 'Age Band'[Age] < _bandTail
        )
    )

     pbix: prattja9.pbix

     

5 Replies

    • prattja9's avatar
      prattja9
      Frequent Visitor
      3 years ago

      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?

      • prattja9's avatar
        prattja9
        Frequent Visitor
        3 years ago

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

         

        Thanks for your help!