I have 2 reports; one is our incident log; and one if our claims List report. Incident log covers all incidents and claims list report only cover the incidents we claim through insurance company. I want to create a column to show the updated claim status in incident log.
My requirements as per below:
1.1. If QBE No in incident register report is not blank then perfrom LOOKUPVALUE with Claims List report Broker Ref column to populate the claim status. LOOKUPVALUE('Claims List'[Claim Status],'Claims List'[Broker Ref],'Incident Register'[QBE No.]),
1.2. However,If QBE No and Broker Ref is not match then show as "QBE No Not Match"
2.1.if QBE no" column in incident register report is empty then, refer to "At Fault (Y/N/Unsure)" column in incident register report, then Y populate as Fault, N popolate as no Fault, U populate as Open, if blank populate as Open
Below is the function wrote by me but it is not working. It shows as blank when QBE no and Broker Ref is not match which i expected it to show as "QBE No Not Match"
Claim Status Measure =
VAR CurrentQBE = 'Incident Register'[QBE No.]
RETURN
IF(
ISBLANK(CurrentQBE),
SWITCH(
'Incident Register'[At Fault (Y/N/Unsure)],
"Y", "Fault",
"N", "No Fault",
"U", "Open",
"Open"
),
IF(
True(),
LOOKUPVALUE('Claims List'[Claim Status],'Claims List'[Broker Ref],'Incident Register'[QBE No.])=1,
"QBE No Not Match"
)
)
