Count by Catagory

[ravitejaballa]

Hi,

I trying to get number of DeviceID for a each catagory (binLegendColumn).

But it has to be for latest value for a given timestamp.

As you can see below, i am able to fetch latest hours value (<= timestamp)

From this, expected output

binLegend	number of distinct deviceid
< 0 Hours	1
0 - 24 Hours	2
25 - 100 Hours	1
> 100 Hours	1

my pbix file:

https://1drv.ms/u/s!AhI1WOiXwAe-qNl6FDPyeZvIv-Xzew?e=98X6Fa

Here is my sample data.

DeviceID	lastValueHours	binLegendColumn	timestamp
A	-345	< 0 Hours	7/13/2020 0:00
A	-150	< 0 Hours	7/12/2020 0:00
A	0	0 - 24 Hours	7/11/2020 0:00
A	20	0 - 24 Hours	7/10/2020 0:00
A	50	25 - 100 Hours	7/9/2020 0:00
A	200	> 100 Hours	7/8/2020 0:00
B	100	25 - 100 Hours	7/12/2020 0:00
B	70	25 - 100 Hours	7/11/2020 0:00
B	24	0 - 24 Hours	7/10/2020 0:00
B	0	0 - 24 Hours	7/9/2020 0:00
B	-300	< 0 Hours	7/8/2020 0:00
C	23	0 - 24 Hours	7/13/2020 0:00
C	0	0 - 24 Hours	7/12/2020 0:00
C	0	0 - 24 Hours	7/11/2020 0:00
C	-100	< 0 Hours	7/10/2020 0:00
C	-250	< 0 Hours	7/9/2020 0:00
D	200	> 100 Hours	7/12/2020 0:00
D	100	25 - 100 Hours	7/11/2020 0:00
D	20	0 - 24 Hours	7/10/2020 0:00
D	-5	< 0 Hours	7/9/2020 0:00
D	-150	< 0 Hours	7/8/2020 0:00
E	22	0 - 24 Hours	7/12/2020 0:00
E	5	0 - 24 Hours	7/11/2020 0:00
E	2	0 - 24 Hours	7/10/2020 0:00
E	-15	< 0 Hours	7/9/2020 0:00
E	-25	< 0 Hours	7/8/2020 0:00

bin	binLegend
1	< 0 Hours
2	0 - 24 Hours
3	25 - 100 Hours
4	> 100 Hours

[v-diye-msft]

Hi @ravitejaballa 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. If not, please kindly elaborate more. thanks!

[amitchandak]

@ravitejaballa , You can try measures like

m1 =lastnonblankvalue(Table[timestamp],max(Table[binLegendColumn]))

m2 = lastnonblankvalue(Table[timestamp],max(Table[lastValueHours]))

[ravitejaballa]

@amitchandak 

Thanks for reply,

I already have these measure in my report.

Using this measure, i want to get count of number of deviceID for each legend.

Like below.

[harshnathani]

Hi @ravitejaballa ,

Based on your Data No of Distinct Device Id are as below.

In such case you can just use.

(DISTINCTCOUNT(deviceHours[DeviceID])

How did you get 1 , 2, 1, 1 .

Let me know if I am missing something.

Regards

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[ravitejaballa]

@harshnathani 

Yes, you are missing one thing.

It has to be latest value for a given timestamp.

you can refer my sample file.

https://1drv.ms/u/s!AhI1WOiXwAe-qNl6FDPyeZvIv-Xzew?e=98X6Fa

[harshnathani]

Hi @ravitejaballa ,

Try this measure

TestMeasure = 
var a = CALCULATE(MAX(deviceHours[timestamp]),ALLSELECTED())
var b = CALCULATE(DISTINCTCOUNTNOBLANK(deviceHours[DeviceID]),FILTER(deviceHours,deviceHours[timestamp] =  a))

RETURN

b

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[ravitejaballa]

@harshnathani 

Thank for reply,

I think we are close to solution.

You solution work for timestamp (7/11/2020) as we have data on this day for all devices.

But as you can see from below for timestamp - 7/13/2020.

It failed, because for binLegend it took max date but we need to get last availabe date in the given range.

Excepted output:

[harshnathani]

Hi @ravitejaballa ,

Incase you want the last Available Date, just remove ALLSELECTED()

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[harshnathani]

Hi @ravitejaballa ,

Your filter context is BinLegend and you are looking for Last Date by Id ?

Do you want the last visible date by BinLegend  or By ID ?

Not sure if the slicer will work on this. Need to check.

This gives me the MAX Date of the BIN Legend, but you need the dates by ID ?

Not sure but can I know the business insights which this will give .  Just curious.

Regards,

Harsh Nathani

[ravitejaballa]

@harshnathani 

I need how many number of deviceIDs falls into each binLegend.

For a given timestamp range, we need to get latest/last avaiable value (LatestHours/binLegendMeasure).

and

count number of deviceIDs in each binLegend

[ravitejaballa]

@harshnathani @amitchandak 

Is there a way to generate below table and group by "binLegendMeasure" and get count of DeviceID  in measure?

[ravitejaballa]

@harshnathani @amitchandak 

Understood my requirement?

Is it possible in power bi ?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  i have solve it using measures only.

Hope this helps.

[ravitejaballa]

@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
    )
)

[Ashish_Mathur]

You are welcome.

[Anonymous]

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)

[ravitejaballa]

@Anonymous 

Thanks for reply.

My case is dynamic with time range.

So measure is better option.

[ravitejaballa]

@harshnathani  Tried that but failed.