Calculated Column with IF Statement across two tables

[AvPowerBI]

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:

Pending CMTD or PMTD =

IF('Fact'[Outcome] ="Pending" && 'Calendar'[Current MTD]=1 || 'Calendar'[Previous MTD]=1,"Yes","No")

Thanks

[OliT]

Hi @AvPowerBI 

Is your problem solved? Do you still need help now?

Regards,

OliT

[Ashish_Mathur]

Hi,

What is the purpose of creating a calculated column formula?  Why not a measure?  What exactly are you trying to achieve?

[PowerAuto82]

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.

[Ashish_Mathur · Accepted 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.

[PowerAuto82]

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

[Ashish_Mathur]

Hi,

Ensure you write that as a calculated column formula (not as a measure).