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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
dsandip
Frequent Visitor

IF else is not working

Hi,

I have a sql query condition like below:

CASE
WHEN Right(OPS_CNDCT_REF_PROFIT_CENTRE_NM,8)='Casualty' And Left(INSURED_NM,10)='Knighthood' THEN 'Y'
WHEN (Left(POLICY_NO,4)='0067' Or Left(POLICY_NO,4)='0065') and OPS_CNDCT_REF_PROFIT_CENTRE_NM like '%OCEAN MARINE%' THEN 'Y' ELSE 'N' END
AS "AIG_controlled_Indicator"

 

I am converting them to this below statement:

Column 3 =
IF(
    Right(TDIM_OPS_CNDCT_REF_PROFIT_CENTRE_PBI[OPS_CNDCT_REF_PROFIT_CENTRE_NM],8)="Casualty" && Left(TDIM_POLICY_PBI[INSURED_NM],10)="Knighthood", "Y", if(
            (Left(TDIM_POLICY_PBI[POLICY_NO],4)="0067" || Left(TDIM_POLICY_PBI[POLICY_NO],4)="0065") &&
            CONTAINSSTRING(TDIM_OPS_CNDCT_REF_PROFIT_CENTRE_PBI[OPS_CNDCT_REF_PROFIT_CENTRE_NM], "OCEAN MARINE")
            == TRUE() , "Y")            

)
 
But it gives me the below error:
A single value for column 'POLICY_NO' in table 'TDIM_POLICY_PBI' 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.
 
what is wrong here?
 
Thanks,
Sandip
2 REPLIES 2
Greg_Deckler
Super User
Super User

@dsandip In measures, you need to wrap column references with an aggregator like MAX, MIN, SUM, AVERAGE, etc.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi,

I am not using Measure, rather I am using Calculated column here.

So, what I have to do now.

 

Thanks,

Sandip

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.