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,
I have a fact table that joins to a calendar table.
I want to created a calculated column but I am getting the following message
A single value for column 'Current MTD' in table 'Calendar' 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.
My DAX formula is the following:
Solved! Go to Solution.
Hi,
Via the RELATED() function, bring over the CMTD and PMTD columns from the Calendar Table to the Fact Table and then write this calculated column formula in the Fact table
Pending CMTD or PMTD =
IF('Fact'[Outcome] ="Pending" && ('Fact'[Current MTD]=1 || 'Fact'[Previous MTD]=1),"Yes","No")
Hope this helps.
Hi,
What is the purpose of creating a calculated column formula? Why not a measure? What exactly are you trying to achieve?
I did try and do a mesaure but got the same error.
I basically want to add this calculated column to the filter pane, so the end user can select Yes or No based on the If Statement condition
When I add the three individual fields in question
[Outcome]
[Current MTD]
[Previous MTD]
selecting the [Outcome] of Pending works
but when I select [Current MTD] to 1 then [Previous MTD] will only show values of 0 and vice versa when I select [Previous MTD] to 1 the only values I see for [Current MTD] are 0
Thats why I want have this new calculated column that does it but it is not working.
Hi,
Via the RELATED() function, bring over the CMTD and PMTD columns from the Calendar Table to the Fact Table and then write this calculated column formula in the Fact table
Pending CMTD or PMTD =
IF('Fact'[Outcome] ="Pending" && ('Fact'[Current MTD]=1 || 'Fact'[Previous MTD]=1),"Yes","No")
Hope this helps.
Hi,
I have tried the following DAX based on your recommedations
Pending CMTD or PMTD =
IF(
'Fact'[Outcome] ="Pending"
&& (
RELATED('Calendar'[Current MTD])=1
||
RELATED('Calendar'[Previous MTD])=1
)
,"Yes"
,"No"
)
But I still get the error message:
A single value for column 'Outcome' in table 'Fact' 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.
Is there something wrong with the DAX formula I have edited?
Thanks
Hi,
Ensure you write that as a calculated column formula (not as a measure).
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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |