Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Update Claim Status with if function and lookupvalue function

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Sample date from incident register report

     

    Incident
    Date
    YearQBE NumberAt Fault (Y/N/Unsure)
    02/01/20232023  
    03/01/20232023  
    05/01/20232023  
    06/01/20232023  
    17/01/20232023  
    19/01/20232023  
    23/01/20232023  
    25/01/20232023  
    03/02/20232023  
    06/02/20232023  
    06/02/20232023  
    10/02/20232023  
    21/02/20232023  
    28/02/20232023  
    09/03/20232023  
    10/03/20232023  
    13/03/20232023  
    20/03/20232023  
    22/03/20232023  
    24/03/20232023  
    03/04/20232023  
    04/04/20232023  
    05/04/20232023  
    07/04/20232023  
    26/04/20232023  
    27/04/20232023  
    28/04/20232023  
    02/05/20232023  
    04/05/20232023  
    04/05/20232023  
    08/05/20232023  
    10/05/20232023  
    10/05/20232023  
    18/05/20232023  
    22/05/20232023  
    25/05/20232023QBE04008099/QBE04008142 
    09/06/20232023QBE04017127 
    12/06/20232023QBE04021906 
    13/06/20232023QBE04024895 
    13/06/20232023QBE04020898 
    20/06/20232023QBE04020829 
    21/06/20232023QBE04022177 
    21/06/20232023QBE04021461 
    28/06/20232023QBE04027518 
    28/06/20232023QBE04028368 
    29/06/20232023QBE04029138 
    29/06/20232023QBE04029610 
    29/06/20232023QBE04029947 
    03/07/20232023QBE04032019 
    04/07/20232023QBE04033901 
    05/07/20232023QBE04040762 
    05/07/20232023QBE04034796 
    05/07/20232023QBE04034481 
    11/07/20232023QBE04040731 
    12/07/20232023QBE04041922 
    14/07/20232023QBE04042395 
    20/07/20232023QBE04047371 
    20/07/20232023QBE04047555 
    24/07/20232023QBE04050422 
    26/07/20232023QBE04076366 
    27/07/20232023QBE04055268 
    02/08/20232023QBE04061693 
    09/08/20232023QBE04068284 
    09/08/20232023QBE040469163 
    11/08/20232023QBE04075770 
    17/08/20232023QBE04076237 
    25/08/20232023QBE04083256 
    25/08/20232023QBE04083716 
    01/09/20232023QBE04087984 
    01/09/20232023N/A 
    01/09/20232023QBE04088352 
    07/09/20232023QBE04093028Y
    10/09/20232023QBE04107144U
    12/09/20232023QBE04099018Y
    14/09/20232023QBE04105609Y
    19/09/20232023QBE04104364Y
    20/09/20232023QBE04105610N
    25/09/20232023QBE04110302Y
    27/09/20232023  
    27/09/20232023QBE04112323Y
    03/10/20232023QBE04122650Y
    03/10/20232023N/AY
    04/10/20232023QBE04118602Y
    05/10/20232023QBE04120771U
    09/10/20232023QBE04123724Y
    10/10/20232023QBE04124251U
    11/10/20232023QBE04126089U

     

    Sample date from claim list report

     

    Broker RefClaim Status
    QBE04112323Open
    QBE04110302Open
    QBE04107144Open
    QBE04105610Open
    QBE04104364Open
    QBE04105609Open
    QBE04099018Open
    QBE04093028Open
    QBE04088352Open
    QBE04087984Open
    QBE04083716No Claim
    QBE04083256Open
    QBE04076237No Claim
    QBE04075770Open
    QBE04068284Open
    QBE04069163Open
    QBE04061693Open
    QBE04055268Open
    QBE04076366Open
    QBE04050422Open
    QBE04047555Open
    QBE04047371No Claim
    QBE04042395Open
    QBE04041922Open
    QBE04040731No Claim
    QBE04040762Open
    QBE04034481Open
    QBE04034796Open
    QBE04033901Open
    QBE04032019Open
    QBE04029138Open
    QBE04029610Open
    QBE04029947Open
    QBE04028368No Claim
    QBE04027518Open
    QBE04022177Open
    QBE04021461Open
    QBE04020829Open
    QBE04024895Open
    QBE04020898Open
    QBE04021906Fault
    QBE04017127Open
    QBE04008142Open
    QBE03996817Open
  • Anonymous's avatar
    Anonymous
    Not applicable

    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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Anonymous 

       

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