Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a problem which I can't resolve. Case is that we have machines with several measurement points per machine and three different statuses (=warning types). I have COUNTROWS measure for each warning type and those are in table visual where every machine has it's own row.
Problem is how can I get latest warning type for each measurement point based on latest measured datetime? It would be easy with just LASTDATE function but when measurement dates differ between measurement point this solution won't work. I need to group measurement point and date or datetime with somehow. I tried just GROUPBY( alarms_table, measurement_point, date ) in CALCULATE function but that won't work.
Here is quick sample data that I just made with excel. I highlighted latest measurement date for point P02 which is the problem in this example.
Can someone help? Thanks!
Solved! Go to Solution.
@mgz_00
Please try
Ok =
SUMX (
SUMMARIZE ( 'Table', 'Table'[Machine_ID], 'Table'[Measurement_Point] ),
INT (
NOT ISEMPTY (
FILTER (
TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ),
'Table'[Warning_Type] = "Ok"
)
)
)
)
Hi @mgz_00
please try
Latest Warning =
MAXX (
TOPN ( 1, FILTER ( 'Table', 'Table'[Warning Type] <> "Ok" ), 'Table'[DateTime] ),
'Table'[Warning Type]
)
Thank you, I will try that. But I am counting all warning types because there can be several alarms active from several different measurement source system. So basically
I need to count latest warning types from every different source. Here is new picture:
@mgz_00
I think I misunderstood the requirement. I believe you want to create three measures (one for each status) to count based on the latest status of each warning type. You can use the following measure structure for "Danger" and "Warning" as well.
Ok =
SUMX (
VALUES ( 'Table'[Measurement_Point] ),
INT (
NOT ISEMPTY (
FILTER (
TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ),
'Table'[Warning_Type] = "Ok"
)
)
)
)
Thank you so much, this seems to work perfectly! When I get more data I can validate this better. One thing though. This works in table rows but in totals it won't count all latest warning types from all machines. So basically it need some tweaking when counting totals like this
Ok =
VAR _latestAlarm =
SUMX (
VALUES ( 'Table'[Measurement_Point] ),
INT (
NOT ISEMPTY (
FILTER (
TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ),
'Table'[Warning_Type] = "Ok"
)
)
)
)
VAR _countTotal =
previous code with some changes
RETURN
IF(
HASONEVALUE( machine_id ),
_latestAlarm,
_countTotal
)
@mgz_00
Please try
Ok =
SUMX (
SUMMARIZE ( 'Table', 'Table'[Machine_ID], 'Table'[Measurement_Point] ),
INT (
NOT ISEMPTY (
FILTER (
TOPN ( 1, CALCULATETABLE ( 'Table' ), 'Table'[DateTime] ),
'Table'[Warning_Type] = "Ok"
)
)
)
)
Actually I just figured out at there can be ties in DateTime... So there can be "OK" and "Warning" with same timestamp. Is there easy solution to rank those? Luckily I have one other ID column which I can use to break those ties but I don't know how.
Edit. I think I got it 🙈. Just added after [DateTime]:
" , , 'Table'[Alarm_id] " and it seems to give correct results.
Thank you so much, this has been working solution!
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |