Forum Discussion

Jbridge450's avatar
Jbridge450
Regular Visitor
7 years ago
Solved

Measure error

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.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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.

    Measure_error

     

    Best Regards,
    Dale

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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's avatar
      Jbridge450
      Regular Visitor
      7 years ago

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

      • Jbridge450's avatar
        Jbridge450
        Regular Visitor
        7 years ago

        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 IDSupplier NameCityStateZipRequestorInvoice NumberInvoice DateCheck NumberPayment DatePayment AmountAccounting DateSourcePO NumberBUAccountAliasInvoice AmountCompliant Spend
        12841ADT SECURITY SERVICESPITTSBURGHPA‭15250-7878‬ ‭664959574‬Thursday, July 12, 201813271803Friday, August 24, 201853.27Tuesday, July 31, 2018SUPPLIER 585560702585059$53Non-Compliant
        12841ADT SECURITY SERVICESPITTSBURGHPA‭15250-7878‬ ‭663505040‬Wednesday, June 13, 201813264428Friday, July 27, 2018150.78Friday, June 22, 2018SUPPLIER 1056079810425$151Non-Compliant
        12841ADT SECURITY SERVICESPITTSBURGHPA‭15250-7878‬ ‭663501764‬Wednesday, June 13, 201813264428Friday, July 27, 2018165.73Friday, June 22, 2018SUPPLIER 1065090110420$166Non-Compliant
        12841ADT SECURITY SERVICESPITTSBURGHPA‭15250-7878‬ ‭663465630‬Tuesday, June 12, 201813264428Friday, July 27, 201849.48Monday, July 09, 2018SUPPLIER 585560702585068$49Non-Compliant
        12841ADT SECURITY SERVICESPITTSBURGHPA‭15250-7878‬ ‭663465629‬Tuesday, June 12, 201813264428Friday, July 27, 201849.48Monday, July 09, 2018SUPPLIER 585560702585067$49Non-Compliant
        12841ADT SECURITY SERVICESPITTSBURGHPA‭15250-7878‬ ‭663465627‬Tuesday, June 12, 201813264428Friday, July 27, 201849.48Monday, July 09, 2018SUPPLIER 585560702585061$49Non-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.