topic Re: Update Claim Status with if function and lookupvalue function in DAX Commands and Tips

Update Claim Status with if function and lookupvalue function

Anonymous — Fri, 13 Oct 2023 09:44:33 GMT

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

Re: Update Claim Status with if function and lookupvalue function

Anonymous — Fri, 13 Oct 2023 09:47:33 GMT

Sample date from incident register report

Incident Date	Year	QBE Number	At Fault (Y/N/Unsure)
02/01/2023	2023
03/01/2023	2023
05/01/2023	2023
06/01/2023	2023
17/01/2023	2023
19/01/2023	2023
23/01/2023	2023
25/01/2023	2023
03/02/2023	2023
06/02/2023	2023
06/02/2023	2023
10/02/2023	2023
21/02/2023	2023
28/02/2023	2023
09/03/2023	2023
10/03/2023	2023
13/03/2023	2023
20/03/2023	2023
22/03/2023	2023
24/03/2023	2023
03/04/2023	2023
04/04/2023	2023
05/04/2023	2023
07/04/2023	2023
26/04/2023	2023
27/04/2023	2023
28/04/2023	2023
02/05/2023	2023
04/05/2023	2023
04/05/2023	2023
08/05/2023	2023
10/05/2023	2023
10/05/2023	2023
18/05/2023	2023
22/05/2023	2023
25/05/2023	2023	QBE04008099/QBE04008142
09/06/2023	2023	QBE04017127
12/06/2023	2023	QBE04021906
13/06/2023	2023	QBE04024895
13/06/2023	2023	QBE04020898
20/06/2023	2023	QBE04020829
21/06/2023	2023	QBE04022177
21/06/2023	2023	QBE04021461
28/06/2023	2023	QBE04027518
28/06/2023	2023	QBE04028368
29/06/2023	2023	QBE04029138
29/06/2023	2023	QBE04029610
29/06/2023	2023	QBE04029947
03/07/2023	2023	QBE04032019
04/07/2023	2023	QBE04033901
05/07/2023	2023	QBE04040762
05/07/2023	2023	QBE04034796
05/07/2023	2023	QBE04034481
11/07/2023	2023	QBE04040731
12/07/2023	2023	QBE04041922
14/07/2023	2023	QBE04042395
20/07/2023	2023	QBE04047371
20/07/2023	2023	QBE04047555
24/07/2023	2023	QBE04050422
26/07/2023	2023	QBE04076366
27/07/2023	2023	QBE04055268
02/08/2023	2023	QBE04061693
09/08/2023	2023	QBE04068284
09/08/2023	2023	QBE040469163
11/08/2023	2023	QBE04075770
17/08/2023	2023	QBE04076237
25/08/2023	2023	QBE04083256
25/08/2023	2023	QBE04083716
01/09/2023	2023	QBE04087984
01/09/2023	2023	N/A
01/09/2023	2023	QBE04088352
07/09/2023	2023	QBE04093028	Y
10/09/2023	2023	QBE04107144	U
12/09/2023	2023	QBE04099018	Y
14/09/2023	2023	QBE04105609	Y
19/09/2023	2023	QBE04104364	Y
20/09/2023	2023	QBE04105610	N
25/09/2023	2023	QBE04110302	Y
27/09/2023	2023
27/09/2023	2023	QBE04112323	Y
03/10/2023	2023	QBE04122650	Y
03/10/2023	2023	N/A	Y
04/10/2023	2023	QBE04118602	Y
05/10/2023	2023	QBE04120771	U
09/10/2023	2023	QBE04123724	Y
10/10/2023	2023	QBE04124251	U
11/10/2023	2023	QBE04126089	U

Sample date from claim list report

Broker Ref	Claim Status
QBE04112323	Open
QBE04110302	Open
QBE04107144	Open
QBE04105610	Open
QBE04104364	Open
QBE04105609	Open
QBE04099018	Open
QBE04093028	Open
QBE04088352	Open
QBE04087984	Open
QBE04083716	No Claim
QBE04083256	Open
QBE04076237	No Claim
QBE04075770	Open
QBE04068284	Open
QBE04069163	Open
QBE04061693	Open
QBE04055268	Open
QBE04076366	Open
QBE04050422	Open
QBE04047555	Open
QBE04047371	No Claim
QBE04042395	Open
QBE04041922	Open
QBE04040731	No Claim
QBE04040762	Open
QBE04034481	Open
QBE04034796	Open
QBE04033901	Open
QBE04032019	Open
QBE04029138	Open
QBE04029610	Open
QBE04029947	Open
QBE04028368	No Claim
QBE04027518	Open
QBE04022177	Open
QBE04021461	Open
QBE04020829	Open
QBE04024895	Open
QBE04020898	Open
QBE04021906	Fault
QBE04017127	Open
QBE04008142	Open
QBE03996817	Open

Re: Update Claim Status with if function and lookupvalue function

Anonymous — Tue, 17 Oct 2023 08:57:51 GMT

Hi Anonymous ,

Please update the formula of measure [Claim Status Measure] as below and check if that can return the expected result...

Claim Status Measure = VAR CurrentQBE = 'Incident Register'[QBE No.] VAR LookupResult = LOOKUPVALUE('Claims List'[Claim Status],'Claims List'[Broker Ref], CurrentQBE) VAR AtFaultStatus = SWITCH('Incident Register'[At Fault (Y/N/Unsure)], "Y", "Fault", "N", "No Fault", "U", "Open", BLANK(), "Open") RETURN IF( ISBLANK(CurrentQBE), AtFaultStatus, IF( ISBLANK(LookupResult), "QBE No Not Match", LookupResult ) )

Best Regards

Re: Update Claim Status with if function and lookupvalue function

Anonymous — Tue, 17 Oct 2023 10:28:10 GMT

Anonymous

Thanks for help but i show error as "A table of multiple values was supplied where a single value was expected."