countrows range measure

[kirkjordan]

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 between £0 - £500, £501 - £1000, £1001 – £2000 and so on. Still new to PBI so the less complicated the better

Regards,

[AlB]

@kirkjordan 

Can you share the pbix? Or one with dummy data that reproduces the problem?

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 

[kirkjordan]

Got it working, thanks for your help!!!

[kirkjordan]

Hi @AlB 

Not sure if I did that correctly, getting an error

[AlB · Accepted solution]

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