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.
hi,
can anyone help me to explain what this error message means & how to resolve it -
'A single value for column 'Date' in table 'msjd-cldr' 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.'
it's when i'm actually trying to get the year from 'Date' field in a date table using the DAX function - YEAR. It works in another instance in the same file though.
tks & krgds, -nik
Solved! Go to Solution.
Right, you would need something like:
Monthly.MFRR =
SWITCH (
TRUE (),
YEAR(MAX('cldr'[Date])) = 2017, DIVIDE ( [NLQA.EM], [AAE16] ),
YEAR(MAX('cldr'[Date])) = 2018, DIVIDE ( [NLQA.EM], [AAE17] )
)
If NLQA.EM and AAE16 and AAE17 are colums, you will need an aggregation around them as well.
If in a measure filter you try to use columns from two tables. You need to use some function to give one value.
First one will not work
filter(Date,Date[Date]<Sales[Sales])
filter(Date,Date[Date]<max(Sales[Sales]))
Would need to see sample data and formul to be certain but this usually happens when you refere to a column in a measure without specifying an aggregation like MAX, MIN, SUM, etc. or you are using somthing like FILTER that returns a table of values. Very difficult to know without the formula.
hi @Greg_Deckler & @amitchandak ,
the dax formula is
YR = YEAR('cldr'[Date])
where i'm trying to get the year from [Date] in date table 'cldr'.
actually, i'm doing so as part of a test for the following formula:
Monthly.MFRR =
SWITCH (
TRUE (),
YEAR('cldr'[Date]) = 2017, DIVIDE ( [NLQA.EM], [AAE16] ),
YEAR('cldr'[Date]) = 2018, DIVIDE ( [NLQA.EM], [AAE17] )
)
may b i havent'r specified the aggregation to a column in a measure as u mentioned.
krgds, -nik
Right, you would need something like:
Monthly.MFRR =
SWITCH (
TRUE (),
YEAR(MAX('cldr'[Date])) = 2017, DIVIDE ( [NLQA.EM], [AAE16] ),
YEAR(MAX('cldr'[Date])) = 2018, DIVIDE ( [NLQA.EM], [AAE17] )
)
If NLQA.EM and AAE16 and AAE17 are colums, you will need an aggregation around them as well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |