Count by Catagory

if a Power Query approach is accepted, here it is one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFLDoUgDEX3wlhiWyTq0A+rMO5/G88+CFKuI5LT5p7ecF1uc4PzYYrPM48cRiEhdw9lwJHyQOygULZUCiaLS8baL2e8vHh/ABMalc+oVCwTKPd632qhD6g8NCNA+ePtKJ+ULfX1bOoGgu3Ppn0bf7b1ud+HeMU+foXXb2uKJg0RcCqOYPwvgzDl6N6Yck0jvH8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DeviceID = _t, lastValueHours = _t, timestamp = _t]),
    #"Changed Type" = Table.Partition(Table.TransformColumnTypes(Source,{{"DeviceID", type text}, {"lastValueHours", Int64.Type}, {"timestamp", type date}}),"lastValueHours",4,each Number.From(_<0)*0+ Number.From(_>=0 and _<24)*1+Number.From(_>=24 and _<100)*2+Number.From(_>=100)*3),
    #"Converted to Table" = Table.FromList(#"Changed Type", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "distDev", each List.Count(List.Distinct(_[Column1][DeviceID])))
in
    #"Added Custom"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYopNTAwTlYwUPDILy0qVorViVYyAgobKOgqGJkgCRoDBY1MgaKGBshqTWBGpCJLxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [bin = _t, binLegend = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"bin", Int64.Type}, {"binLegend", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"bin"}, binned, {"Index"}, "binned", JoinKind.LeftOuter),
    #"Expanded binned" = Table.ExpandTableColumn(#"Merged Queries", "binned", {"distDev"}, {"binned.distDev"})
in
    #"Expanded binned"

this is the output got

if you want to change the destination of same value, adapt this expression accordingly:

= Table.Partition(Table.TransformColumnTypes(Source,{{"DeviceID", type text}, {"lastValueHours", Int64.Type}, {"timestamp", type date}}),"lastValueHours",4,each Number.From(_<0)*0+ Number.From(_>=0 and _<24)*1+Number.From(_>=24 and _<100)*2+Number.From(_>=100)*3)

Ashish_Mathur

You are awesome!!

your measure worked for me.

Did small change to get latest available value in given time range.

TestMeasure = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( deviceHours[DeviceID] ),
            "ABCD", CALCULATE (
                [LatestHours],
                CALCULATETABLE ( VALUES ( deviceHours[DeviceID] ) ),
                ALLSELECTED ()
            )
        ),
        COUNTROWS (
            FILTER (
                binMater,
                [ABCD] >= binMater[Lower bin legend]
                    && [ABCD] <= binMater[Upper bin legend]
            )
        ) > 0
    )
)

Anonymous 

Thanks for reply.

My case is dynamic with time range.

So measure is better option.

You are welcome.