Measure error

[Jbridge450]

Hi, I am a very new user to Power BI.  I am encountering an error for the measure below.  Can someone provide insight to the potential cause?  If additional information is needed please let me know.

Medical Compliance = SWITCH(
TRUE(),
SEARCH("medline",'FY18 Combined'[Supplier Name],1,0)>0,"Compliant",
search("mckesson",'FY18 Combined'[Supplier Name],1,0)>0,"Compliant",
if(AND('FY18 Combined'[Supplier#/Employee ID]=103447,'FY18 Combined'[Source]<>"BOAPCARD"),1,0)>0,"Compliant",
"Non-Compliant"
)

The error message states:

A single value for column Supplier#/Employee ID in table FY18 Combined cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.

[Jbridge450]

Works perfectly!!  I really appreciate your response.

John

[Greg_Deckler]

If you refer to a column in a measure, you need to use some sort of aggregation like SUM, MAX, etc. There are many cases where the particular aggregation doesn't matter MAX/MIN because of the row context in which the measure is executing within a visual. 

For example, in your column, you refere to: 'FY18 Combined'[Supplier Name] when this needs to be wrapped in an aggegation like MAX('FY18 Combined'[Supplier Name]).

Can't say specfically in your case without understanding your sample data and how you want to use this measure. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Jbridge450]

Thanks Greg, I will give that a try.  Appreciate the help!

[Jbridge450]

Hi Greg, based on your input of "how to get your question answered quickly" I have re-posted my question as the solution did not seem to work.  So, here is hopefully a better question around why the Measure does not work:

Original question was>  I am encountering an error for the measure below.  Can someone provide insight to the potential cause?  If additional information is needed please let me know.

Medical Compliance = SWITCH(
TRUE(),
SEARCH("medline",'FY18 Combined'[Supplier Name],1,0)>0,"Compliant",
search("mckesson",'FY18 Combined'[Supplier Name],1,0)>0,"Compliant",
if(AND('FY18 Combined'[Supplier#/Employee ID]=103447,'FY18 Combined'[Source]<>"BOAPCARD"),1,0)>0,"Compliant",
"Non-Compliant"
)

The above formula is designed to capture which vendor names are either "compliant" or non-compliant.  The result from this formula would be only that medline and mckesson are compliant.  All others non compliant.  A 100% stack graph would then show the % compliant vs non compliant.

The error message states:

A single value for column Supplier#/Employee ID in table FY18 Combined cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.

Below is additional information to help with the answer>>>>

Data for FY18 Combined table

Supplier#/Employee ID

Supplier Name

City

State

Zip

Requestor

Invoice Number

Invoice Date

Check Number

Payment Date

Payment Amount

Accounting Date

Source

PO Number

BU

Account

Alias

Invoice Amount

Compliant Spend

12841

ADT SECURITY SERVICES

PITTSBURGH

PA

‭15250-7878‬

‭664959574‬

Thursday, July 12, 2018

13271803

Friday, August 24, 2018

53.27

Tuesday, July 31, 2018

SUPPLIER

585

560702

585059

$53

Non-Compliant

12841

ADT SECURITY SERVICES

PITTSBURGH

PA

‭15250-7878‬

‭663505040‬

Wednesday, June 13, 2018

13264428

Friday, July 27, 2018

150.78

Friday, June 22, 2018

SUPPLIER

10

560798

10425

$151

Non-Compliant

12841

ADT SECURITY SERVICES

PITTSBURGH

PA

‭15250-7878‬

‭663501764‬

Wednesday, June 13, 2018

13264428

Friday, July 27, 2018

165.73

Friday, June 22, 2018

SUPPLIER

10

650901

10420

$166

Non-Compliant

12841

ADT SECURITY SERVICES

PITTSBURGH

PA

‭15250-7878‬

‭663465630‬

Tuesday, June 12, 2018

13264428

Friday, July 27, 2018

49.48

Monday, July 09, 2018

SUPPLIER

585

560702

585068

$49

Non-Compliant

12841

ADT SECURITY SERVICES

PITTSBURGH

PA

‭15250-7878‬

‭663465629‬

Tuesday, June 12, 2018

13264428

Friday, July 27, 2018

49.48

Monday, July 09, 2018

SUPPLIER

585

560702

585067

$49

Non-Compliant

12841

ADT SECURITY SERVICES

PITTSBURGH

PA

‭15250-7878‬

‭663465627‬

Tuesday, June 12, 2018

13264428

Friday, July 27, 2018

49.48

Monday, July 09, 2018

SUPPLIER

585

560702

585061

$49

Non-Compliant

Relationships:

FY18 Combined to Categories

Account > Account 1

  *Categories table has the following categories: Medical, Office Supplies, and Household Supplies

FY18 Combined to Alias PBI

Alias > Alias 1

  *Alias PBI table has one alias number associated with multiple reporting groups

Hopefully this makes clear my question.

[v-jiascu-msft · Accepted solution]

Hi @Jbridge450,

I would suggest you use a calculated column rather than a measure in your scenario. And the formula can be optimized by removing IF.

Medical Compliance =
SWITCH (
    TRUE (),
    SEARCH ( "medline", 'FY18 Combined'[Supplier Name], 1, 0 ) > 0, "Compliant",
    SEARCH ( "mckesson", 'FY18 Combined'[Supplier Name], 1, 0 ) > 0, "Compliant",
    AND (
        'FY18 Combined'[Supplier#/Employee ID] = 103447,
        'FY18 Combined'[Source] <> "BOAPCARD"
    ), "Compliant",
    "Non-Compliant"
)

I have added some data to make this example more clear.

Best Regards,
Dale