Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
Works perfectly!! I really appreciate your response.
John
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
Thanks Greg, I will give that a try. Appreciate the help!
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.
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
User | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |