Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi,
You may download my PBI file from here. i have solve it using measures only.
Hope this helps.
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!
@ravitejaballa , You can try measures like
m1 =lastnonblankvalue(Table[timestamp],max(Table[binLegendColumn]))
m2 = lastnonblankvalue(Table[timestamp],max(Table[lastValueHours]))
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.
Hi @ravitejaballa ,
Based on your Data No of Distinct Device Id are as below.
In such case you can just use.
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!
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
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!
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:
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!
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
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
Is there a way to generate below table and group by "binLegendMeasure" and get count of DeviceID in measure?
Hi,
You may download my PBI file from here. i have solve it using measures only.
Hope this helps.
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
)
)
You are welcome.
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)
@Anonymous
Thanks for reply.
My case is dynamic with time range.
So measure is better option.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |