Forum Discussion

dorgal's avatar
dorgal
New Member
3 years ago
Solved

Multiple values where single value was expected

Hi All,

  New to PBI and could do with some assistance please.

 

Trying to replicate a report in PBI that a collegue created in EXCEL.

 

I tried using his existing formula but got error that columns contain multiples where 1 was expected.

 

Below is the formula in excel

=IF(W3="Pass","",IF(O3<>AY3,"",VLOOKUP(B3,'GPS fail data Q1'!A:AC,3,FALSE)))

 

Below is how i tried in Power bi

 

IF('Cork /Global Order Report'[Inspection Outcome]="PASS","",IF('Cork /Global Order Report'[Test Group]<>'Cork /Global Order Report'[Audit/Inspection],"",LOOKUPVALUE(GPSReport_sheet_justq[cabserial_name],GPSReport_sheet_justq[cabserial_name],'Cork /Global Order Report'[Service tag])))
 
Any suggestions would be appreciated

 

 

Need Help
  • eliasayyy's avatar
    eliasayyy
    3 years ago
    IF(
    'Cork /Global Order Report'[Inspection Outcome] = "PASS", 
    BLANK(), 
    IF(
        'Cork /Global Order Report'[Test Group] <> 'Cork /Global Order Report'[Audit/Inspection], 
        BLANK(), 
        LOOKUPVALUE(
            GPSReport_sheet_justq[cabserial_name], 
            GPSReport_sheet_justq[cabserial_name], 
            'Cork /Global Order Report'[Service tag]
        )
    )
)

     

    Things to Check:

    1. Ensure that 'Cork /Global Order Report'[Service tag] matches with GPSReport_sheet_justq[cabserial_name] in terms of data type and content.

    2. Ensure that there are no duplicate values in the GPSReport_sheet_justq[cabserial_name] column. If there are duplicates, the LOOKUPVALUE function will throw an error because it won't know which value to return.

    3. If you expect that there might be multiple matches and you want to handle that scenario, you might need to revise your approach or use other DAX functions to aggregate or filter the data.

    4. if you're still facing issues, consider sharing more details about the data model, relationships, and the expected outcome, so I can assist you further.

1 Reply

  • eliasayyy's avatar
    eliasayyy
    Memorable Member
    3 years ago
    IF(
    'Cork /Global Order Report'[Inspection Outcome] = "PASS", 
    BLANK(), 
    IF(
        'Cork /Global Order Report'[Test Group] <> 'Cork /Global Order Report'[Audit/Inspection], 
        BLANK(), 
        LOOKUPVALUE(
            GPSReport_sheet_justq[cabserial_name], 
            GPSReport_sheet_justq[cabserial_name], 
            'Cork /Global Order Report'[Service tag]
        )
    )
)

     

    Things to Check:

    1. Ensure that 'Cork /Global Order Report'[Service tag] matches with GPSReport_sheet_justq[cabserial_name] in terms of data type and content.

    2. Ensure that there are no duplicate values in the GPSReport_sheet_justq[cabserial_name] column. If there are duplicates, the LOOKUPVALUE function will throw an error because it won't know which value to return.

    3. If you expect that there might be multiple matches and you want to handle that scenario, you might need to revise your approach or use other DAX functions to aggregate or filter the data.

    4. if you're still facing issues, consider sharing more details about the data model, relationships, and the expected outcome, so I can assist you further.