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 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 histog...
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

     

  • 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!

      • grantsamborn's avatar
        grantsamborn
        Icon for Solution Sage rankSolution Sage
        3 years ago

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

    • grantsamborn's avatar
      grantsamborn
      Icon for Solution Sage rankSolution Sage
      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