How to do a calculations only when the mid-step variation is not blank

[sweetstar]

The logic is here:

1. All the calculations only use the same table (event). Columns include:

• event id (primary key of this table), 
• equipment id (foreign key of equipment table), one equipment id in this table have 1 to N event records.
• ReportedSiteDateTime
• Only children events have ParentEventID, Parent Event's ParentEventID column is empty. One Parent event can have 0-N children.
• EventType and Status (Grouped) are categorical.

2. I want to show a table, that every equipment id a row, the other column use a measure, which includes 2 parts, the first part is to get the nearest 's ReportedSiteDateTime's record's Status (Grouped) with the condition EventType= "PICO".

The second part checks this event id has how many children events, and shows different text accordingly.

3. My logic is to get the nearest 's ReportedSiteDateTime's record's event id. 

When the event id is not found, do not do the following steps and just show blank. -- I don't know-how.

use the event id get the Status (Grouped). -- VAR stat

count children of the id with condition children EventType="Deficiency Asset". --I got an issue with the row context and do not have count rows >0.

show text according to the number of children.

Can you correct the following measure for me?

PICO =
VAR eventype = "PICO"
--get the lastest eventid under assigned EventType under current row context(EquipmentID)
VAR eventid =
CALCULATE(MAX('EventDetailCache'[EventID]),
FILTER ( 'EventDetailCache',[ReportedSiteDateTime] = MAX('EventDetailCache'[ReportedSiteDateTime]) ),
'EventDetailCache'[EventType]=eventype)
--get the status of the eventid
VAR stat =
IF(ISBLANK(eventid),BLANK(),
MAXX(FILTER('EventDetailCache',[EventID]=eventid),'EventDetailCache'[Status (Grouped)]))
--count children events of the eventid
VAR numofchild =
IF(ISBLANK(eventid),-1,
COUNTROWS(FILTER(ALL('EventDetailCache'),[ParentEventID]=eventid && [EventType]="Deficiency Asset")))
VAR show_defic =
SWITCH(numofchild,
-1,BLANK(),
BLANK()," - no Deficiency",
0," - no Deficiency",
1," - with Deficiency",
" - with Deficiencies"
)
VAR result = stat & show_defic
RETURN result

[Anonymous · Accepted solution]

@sweetstar 

When the event id is not found, do not do the following steps and just show blank. 

The variable can be:

var = if(facttable[eventid] IN VALUES(eventtable[event[id]), [measure], BLANK())

For more suggestion, please provide a sample pbix, it is difficult to follow all logic and measures just with text.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.