Forum Discussion

kirkjordan's avatar
kirkjordan
New Member
5 years ago
Solved

countrows range measure

Hi all,   New to this so please excuse the ignorance. Trying to create a measurement to count the number of values in the ordervalue column between 2 values for example count the number of rows bet...
  • AlB's avatar
    AlB
    5 years ago

    Hi kirkjordan 

    1. Create an ancillary table Thresholds (no relationships) describing the ranges:

    Range MinVal MaxVal
    0-500 0 500
    500-1000 500 1000
    1000-2000 1000 2000

    make sure MinVal and MaxVal are of type number 

    2. Place Thresholds[Range] in a table visual

    3. Place this measure in the visual

    Measure =
VAR minVal_ = SELECTEDVALUE ( Thresholds[MinVal] )
VAR maxVal_ = SELECTEDVALUE ( Thresholds[MaxVal] )
RETURN
    COUNTROWS (
        FILTER ( Table1, Table1[ordervalue] >= minVal_ && Table1[ordervalue] < maxVal_ )
    )

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

Hi kirkjordan 

1. Create an ancillary table Thresholds (no relationships) describing the ranges:

Range MinVal MaxVal
0-500 0 500
500-1000 500 1000
1000-2000 1000 2000

make sure MinVal and MaxVal are of type number 

2. Place Thresholds[Range] in a table visual

3. Place this measure in the visual

Measure =
VAR minVal_ = SELECTEDVALUE ( Thresholds[MinVal] )
VAR maxVal_ = SELECTEDVALUE ( Thresholds[MaxVal] )
RETURN
    COUNTROWS (
        FILTER ( Table1, Table1[ordervalue] >= minVal_ && Table1[ordervalue] < maxVal_ )
    )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers