Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RN_87
Regular Visitor

Error "this can happen when a measure formula refers to a column that contains many values"

I'm trying create a financial statement and I'm new to PBI. I have a dimension table for my report layout that is connected to a fact table via a relationship at the line name. Some of data flowing into the rows need to be in percentage and other just numbers with commas. I created two measures sumActivity = SUM('fctMapping'[FlipActivity]), and PCTActivity = FORMAT([sumActivity],"0.0%").

RN_87_3-1708701798246.png

 

When I bring the measures into values they populate like this:

RN_87_2-1708701394524.png

The highlighted row represents the row that I want to show up as a percentage. 

 

In the dimReportLayout table there is a column DataFormat that contains the identifier PCT for rows I want to show percentage, NUM for rows as numbers with commas, and NA for rows without any data. 

 

To bring the correct format into the same column I tried creating a new measure 

FormatSwitch = SWITCH(TRUE(),SELECTEDVALUE(dimReportLayout[DataFormat] = "PCT"),[PCTActivity],SELECTEDVALUE(dimReportLayout[DataFormat] = "NUM"),[sumActivity]) and I keep getting the following error:
A single value for column 'DataFormat' in table 'dimReportLayout' 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. 
 
Please help.
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@RN_87 ,Measures like

 

FormatSwitch = SWITCH(TRUE(),SELECTEDVALUE(dimReportLayout[DataFormat]) = "PCT",
[PCTActivity],SELECTEDVALUE(dimReportLayout[DataFormat]) = "NUM"
,[sumActivity])

 

Please use new dynamic String format , This article has good example for what you want to do

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@RN_87 ,Measures like

 

FormatSwitch = SWITCH(TRUE(),SELECTEDVALUE(dimReportLayout[DataFormat]) = "PCT",
[PCTActivity],SELECTEDVALUE(dimReportLayout[DataFormat]) = "NUM"
,[sumActivity])

 

Please use new dynamic String format , This article has good example for what you want to do

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

Thank you. That worked.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors