Forum Discussion

sah259's avatar
sah259
Frequent Visitor
9 years ago
Solved

Filter that shows all possible values

Is there a way to get your filter to list all potential options (even if no one falls into a certain category)?

 

ie. If we are tracking the length of a workshop in days, the filter is showing

0-1

2-7

15-21

 

I want people to know that 8-14 days was an option, but that no one participated in a workshop of that length. That is important data to know that no one falls into a certain category option.

 

It also does not show up in my graphs, even when I check the box "Show items with no data"

  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi sah259

     

    Basically you will have to create a table called LengthBand  that has all the bands for the length of the workshop days

    some thing like

     

    Lengthgroup, Min, Max, Position

    0-1,0,1,1

    2-7,2,7,2

    8-14,8,14,3

    15-30,15,30,4

    > 30,31,99999,5

     

    Then on your fact table create a column that is mapped to one of the bands of the lengthgroup using the expression

     

    LengthGroup= CALCULATE (
    VALUES ( LengthBand[Lengthgroup] ),
    FILTER (
    LengthBand,
    Yourfacttable[columncontaingdays] >= LengthBand[Min]
    && Yourfacctable[Columncontainingdays] <= LengthBand[Max]
    )
    )

     

    use the LengthGroup from the LengthBand table as a slicer. 

     

    Any measure which is displayed will then get filtered according to the LengthGroup in the slicer

     

    If this works please accept this as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi sah259

     

    Basically you will have to create a table called LengthBand  that has all the bands for the length of the workshop days

    some thing like

     

    Lengthgroup, Min, Max, Position

    0-1,0,1,1

    2-7,2,7,2

    8-14,8,14,3

    15-30,15,30,4

    > 30,31,99999,5

     

    Then on your fact table create a column that is mapped to one of the bands of the lengthgroup using the expression

     

    LengthGroup= CALCULATE (
    VALUES ( LengthBand[Lengthgroup] ),
    FILTER (
    LengthBand,
    Yourfacttable[columncontaingdays] >= LengthBand[Min]
    && Yourfacctable[Columncontainingdays] <= LengthBand[Max]
    )
    )

     

    use the LengthGroup from the LengthBand table as a slicer. 

     

    Any measure which is displayed will then get filtered according to the LengthGroup in the slicer

     

    If this works please accept this as a solution and also give KUDOS.

     

    Cheers

     

    CheenuSing