Forum Discussion
Calculated Column with IF Statement across two tables
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:
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.
6 Replies
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.
